Solved VBA to Move String in Cells from End to Beginning

Microsoft Excel 2010 - complete product...
May 21, 2018 at 03:20:11
Specs: Windows 7, 2,4 GHz / 4 GB
Hello,

I have some values that represent a decrease of inventory and when pasting them in excel they come in the following form:

50-
140-
etc.

I made a code to move the - string from the end of the cell to the beginning, so I obtain negative numbers and can use them. This works a bit slow - it took a few seconds for 150 cells, but sometimes I have 1000 cells.

I will post the code below, but my question is: could you suggest a change in code so it would speed things up? Or an alternative code.

Sub MOVEcharacter()
    Dim key As String
    Dim c As Range

Application.ScreenUpdating = False

    key = "-"
    
With ActiveSheet
    Set c = Selection
        
For rw = 1 To c.Rows.Count
    For col = 1 To c.Columns.Count
        If Right(c(rw, col).Value, 1) = key And InStr(c(rw, col), key) > 0 Then
            c(rw, col).Value = Left(c(rw, col).Value, InStr(c(rw, col).Value, key) - 1)
                  On Error GoTo 0
            c(rw, col).Value = key & c(rw, col).Value
            c(rw, col).Value = c(rw, col).Value
        End If
    
    Next
Next
End With

Application.ScreenUpdating = True
End Sub

Thank you in advance.


See More: VBA to Move String in Cells from End to Beginning


✔ Best Answer
May 24, 2018 at 15:19:34
Let's switch gears and consider the following:
Sub MakeTestData()
  Const totalNum = 5000
  Randomize
  [A1] = "Test Data"
  For i = 2 To totalNum + 1
    num = Int(Rnd * 200)
    If num < 100 Then
      Cells(i, 1) = num & "-"
    Else
      Cells(i, 1) = num - 100
    End If
  Next 'i
End Sub


Sub FixNumbers()
  [A:A].TextToColumns Destination:=[A1], DataType:=xlDelimited, TrailingMinusNumbers:=True
End Sub

How To Ask Questions The Smart Way

message edited by Razor2.3



#1
May 21, 2018 at 05:13:07
My VBA skills are just above nil, so I can't help with your VBA question,
but I am curious why your using VBA when a formula like:

=LEFT(A1,LEN(A1)-1)*-1

should work just as well?

Not knowing what else is going on in your sheet,
I tried the above formula on a new sheet with over eight thousand rows (8739)
and the conversion time was almost instantaneous.
Then it was just a matter of Copy/Paste Values.

MIKE

http://www.skeptic.com/


Report •

#2
May 21, 2018 at 06:55:40
I see that your code uses an If statement. I assume that you are doing that because only some cells are like your example (50-, 140-). Is that correct?

Assuming that that is correct, let's address the "slowness" issue first.

I started with this:

                A    
1       50-
2       140-
3       This cell has no hyphen
4       This cell has a hypen - 
5       So does this - - - - one 
6       529-
7       845-

I dragged this data down to Row 100 and then right to Column Z.
2600 entries in total.

I then added these 2 instructions to the code to record the time:

Before Application.ScreenUpdating = False:

Cells(101, 1) = Time

After Application.ScreenUpdating = True:

Cells(101, 2) = Time

The code took less than 1 second to make 1872 changes:

             A                 B
101      8:43:01 AM        8:43:01 AM

I certainly can't explain why the code takes "a few seconds" to do 150 entries when you run it.

OK, so now let's look at your code anyway...

1 - Can you explain why you are checking for the dash twice? Shouldn't this one check be enough?

If Right(c(rw, col).Value, 1) = key Then

If the last character is a dash, then InStr >0 must be true. What is the purpose of the InStr instruction?

If Right(c(rw, col).Value, 1) = key And InStr(c(rw, col), key) > 0

2 - What's the On Error instruction for?

3 - What is the following instruction for?

c(rw, col).Value = c(rw, col).Value

AFAICT, all it does is set the value in the cell to the value that is already in the cell.

As far as changes to your code, if the only strings that have a hyphen as the last character are "numbers" (in other words, nothing like this, where there is a hyphen at the end: "This cell has a hyphen -") then this code should work:

  For rw = 1 To c.Rows.Count
    For col = 1 To c.Columns.Count
        If Right(c(rw, col), 1) = key Then
            c(rw, col) = Left(c(rw, col), Len(c(rw, col)) - 1) * -1
        End If
    Next
  Next

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
May 22, 2018 at 02:12:13
@mmcconaghy

I am using VBA because I generate these tables in temp excel files and entering a formula every time I have a new excel is a pita. Plus, the end user is not really into formulas and a click on a smiley face in QAT for solving "the problem" would be best.

@DerbyDad03

All cells have numbers, some are positive and show no dash or any other character, and the ones with a dash are supposed to be negative.

1 - I modified the code a few times to get the result I needed and forgot to clean it up, that's why double condition. Sorry about that. First I made the code having only the InStr >0 condition, then I added Right and didn't remove InStr

2 - I was receiving an error for only the InStr >0 condition in IF. When added Right, the On Error became obsolete.

3 - TBH I found a code online and modified it to include loop by row and column and my condition. The initial code was to move text from a cell to another and it had that line so I let it be.

Looks like I have to learn how to clean my codes.

Yes, all cells should have numeric characters and most have dash.

Thanks for the tip on testing how long it takes for the macro to run. The results I got, after changing my initial code with the one you cleaned up for me:

           10                11
1     11:57:22 a.m.	11:57:31 a.m.

It took 9 seconds for 150 entries because I actually selected the whole column, instead of just my 150 entries.

So I guess I should do a LastRow for my selected column. I did that and now it takes 2 seconds. Why not instantly, dunno; most certainly some wrongdoing on my side, but no idea what. This is how my code looks now:

    Dim key As String
    Dim c As Range
    Dim LastRow As Long
    Dim myCol As Long

Cells(1, 10) = Time
Application.ScreenUpdating = False

    key = "-"
    
With ActiveSheet
    Set c = Selection
    myCol = c.Columns.Column
    LastRow = .Cells(Rows.Count, myCol).End(xlUp).Row
    
For rw = 1 To LastRow
    For col = 1 To c.Columns.Count

    If Right(c(rw, col), 1) = key Then
         c(rw, col) = Left(c(rw, col), Len(c(rw, col)) - 1) * -1
    End If
    
    Next
Next
End With

Application.ScreenUpdating = True
Cells(1, 11) = Time

message edited by Mrrrr


Report •

Related Solutions

#4
May 22, 2018 at 06:24:36
2600 cells, alternating between a positive number and a number with a trailing hyphen, i.e. 1300 changes to make, as well as incrementing a counter for each change:

Less than 1 second with your current code.

31,200 cells, alternating between a positive number and a number with a trailing hyphen, i.e. 15,600 changes to make, as well as incrementing a counter for each change:

About 1 second with your current code.

No way for me, sitting out here on the ole interweb, to know why it takes so long in your situation.

Are you testing your code against downloaded data or just numbers that you entered in Excel like I am? Maybe that has something to do with it.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
May 22, 2018 at 07:03:46
Suggestion:
Application.EnableEvents = False
?

How To Ask Questions The Smart Way


Report •

#6
May 22, 2018 at 08:41:06
re: Application.EnableEvents = False

That might work if there are Event macros associated with the worksheet/workbook in question. If not, it won't do anything.

In addition, if you do turn events off (=False) make sure you turn them back on before the code exits (=True).

Unlike ScreenUpdating, Events do not automatically become enabled when the code exits. An error handling routine is recommended to help ensure that Events are set to True if the code stumbles upon an error and wants to quit after the False instruction is executed.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
May 22, 2018 at 23:01:11
There are no Event macros associated with the workbook in question.

Are you testing your code against downloaded data or just numbers that you entered in Excel like I am? Maybe that has something to do with it.

I am extracting data from a software. I just extracted 1200 entries now. I am using the code from my personal.xlsb macro file, because the temp (but saved) excel I am using for the extracted data is just a normal excel file. I need it to run from personal.xlsb, but I'm gonna test both to see if any change (shouldn't be I think).

- running from personal.xlsb - 1200 entries - back to 9 seconds --- 8:31:15 a.m. 8:31:24 a.m.
- running from temp excel saved as xlsm - 1200 entries - 9 seconds on the clock --- 8:33:35 a.m. 8:33:44 a.m.

Must be the configuration I'm running on. 4 GB RAM laptop with 4 cores CPU.
I'm gonna take this excel home and see if any difference on 16 GB RAM and 8 cores PC.
I will come with feedback later.

Thanks for all the suggestions so far.


Report •

#8
May 23, 2018 at 06:08:31
Just for comparison, I've been running it under this configuration:

Windows 7, Excel 2010
Intel Core i5-4570 @ 3.2 GHz, 4 Cores, 16 GB RAM

I can test it at home tonight, but right now I can't recall the configuration of that machine.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
May 23, 2018 at 07:23:14
This must be the problem. At home under:

Windows 7, Excel 2013
AMD-FX 8300 @ 3,3 GHz, 8 cores, 16 GB RAM

it works "instantly" for 1200 entries (of which 1000 were with dash): 5:18:52 p.m. 5:18:52 p.m.

At work I have
Windows 7, Excel 2013
Intel Core i3-3110M @ 2.4 GHz, 4 cores, 4 GB RAM

AFAIK it is the processor speed that gives the calculation power, but could it also be the RAM?

message edited by Mrrrr


Report •

#10
May 23, 2018 at 07:46:54
Depends. Do you hear the HDD going crazy while the calculations are going?

How To Ask Questions The Smart Way


Report •

#11
May 23, 2018 at 09:14:00
No. But I will listen more carefully tomorrow. I will also check disk usage for any abnormalities.

Before posting here I tested other codes doing more than just moving a string. And they worked faster, so I assumed something was off in the code.


Report •

#12
May 23, 2018 at 10:48:26
Here's a thought:

Disable page swapping altogether. If the system runs out of memory, it will surely let you know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#13
May 23, 2018 at 11:03:28
re: "AFAIK it is the processor speed that gives the calculation power, but could it also be the RAM?"

I vote RAM. I don't think that there is enough "processing" going on for it to be a problem for a system with a 4 core CPU. Checking for a character at the end of a text string and then building a new one isn't exactly a rocket science type calculation.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#14
May 23, 2018 at 16:48:31
Home Test:

CPU: i3-2120, 3.30 GHz, 2 cores, 4 Gb RAM

Same memory as your work system, half the cores.

2600 entries, 2600 changes, took less than a second.

Could it be the data that you extracted from the "software"? Did you use the exact same data at home when it ran faster?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#15
May 23, 2018 at 17:17:23
Possibly some stray macro in your work PERSONAL.XLSB ??

MIKE

http://www.skeptic.com/


Report •

#16
May 24, 2018 at 03:05:23
My work CPU is i3-3110M @ 2.4 GHz, 4 cores. So a bit slower than yours. RAM is 4 GB but 3,6 GB usable (the rest must be allocated for video card).

No HDD sounds of any kind, laptop is not hot on the bottom either.

Disable page swapping altogether. If the system runs out of memory, it will surely let you know.
Unfortunately admin rights are required so I can't do this unless I ask IT dept.

Possibly some stray macro in your work PERSONAL.XLSB ??
I think not. All my macros are button based, so none are running on workbook open.
Can you give some example that could result in stray code running without my knowledge?

CPU is constantly at >15% usage due to some background (admin) applications. I'm guessing Kaspersky, but could be something else.

I've asked someone from IT dept. to uninstall Kaspersky and put something else. I will post feedback asap.


Report •

#17
May 24, 2018 at 06:13:13
You mentioned Admin Rights, so I assume that you have some type of locked-down corporate image. Trust me, I know that feeling. (Trust me, I know that feeling! I'm surprised I can still access this forum from work.)

2 things come to mind:

1 - re: "Can you give some example that could result in stray code running without my knowledge?"

Based on the assumption of a corporate image, there could be some global application level event macros running. If VBA is set-up properly, they could be completely hidden from view and you'd never know. I assume (dangerous) that disabling events locally would prevent them from running, but nothing would surprise me when it comes to corporate images.

Read the summary here:

https://support.microsoft.com/en-us...

2 - Again, based on the assumption of a corporate image...

Do your applications run on your local machine or do they run in the cloud? My company tried the cloud based method for a while. Not exactly Office 365, but similar in that no applications were stored or run locally. Everything resided on a server(s) and were accessed over the network. The advantage was that we could access our own "virtual desktop" from any machine on our global corporate network or even from home and it would always look exactly the same. The disadvantage was that it sucked. Our corporate network couldn't handle all the traffic so everything was slow, there were too many outages, etc. If we lost connectivity, we often had to "reboot" the virtual machine before we could log back in. Depending on the condition of the network at the time, this could sometimes take 10+ minutes.

They eventually abandoned the cloud configuration and we are back to locally run apps. Most of the data that we work with is still out in the cloud, but without all the application level traffic on the wires, the outages are few and far between.

Could that be causing the slowness on your work machine?

message edited by DerbyDad03


Report •

#18
May 24, 2018 at 15:19:34
✔ Best Answer
Let's switch gears and consider the following:
Sub MakeTestData()
  Const totalNum = 5000
  Randomize
  [A1] = "Test Data"
  For i = 2 To totalNum + 1
    num = Int(Rnd * 200)
    If num < 100 Then
      Cells(i, 1) = num & "-"
    Else
      Cells(i, 1) = num - 100
    End If
  Next 'i
End Sub


Sub FixNumbers()
  [A:A].TextToColumns Destination:=[A1], DataType:=xlDelimited, TrailingMinusNumbers:=True
End Sub

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#19
May 24, 2018 at 17:12:31
Nice find, Razor2.3! That's a pretty obscure argument.

Terrible documentation though.

From :

https://msdn.microsoft.com/en-us/li...

TrailingMinusNumbers Optional Variant. Numbers that begin with a minus character.

That's all you get. No Help file that I can find explains how it works or what its for. (Converting older accounting systems data, I guess)

As far as I can tell, it's making an assumption that the user want to convert e.g. 21- to -21 since the default is True.

It seems like even Excel isn't familiar with this feature.

Start with this in A1:A2

        A
1    21- 23
2    -21 23

Manually do a Text-To-Columns, Delimited.
The Preview pane shows:

1    21- 23
2    -21 23

Click Next to choose Space as the Delimiter.
The Preview pane shows no change to 21-

1    21- | 23
2    -21 | 23

Click Next, it still shows:

1    21- | 23
2    -21 | 23

Now click Finish and you end up with:

       A        B
1    -21       23
2    -21       23

That argument is so obscure that even some parts of Excel don't know about it. ;-)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#20
May 24, 2018 at 18:37:51
It helps that the GUI's verbiage is better, "Settings used to recognize numeric data -> Trailing minus for negative numbers." Not sure I'd know what TrailingMinusNumbers did if it wasn't for the macro recorder, especially since that documentation is perfectly wrong.

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#21
May 24, 2018 at 23:56:26
re: Based on the assumption of a corporate image, there could be some global application level event macros running.
I'd dare to think not. As far as I know, before I came with my ideas last year they didn't even use VBA at all.

re: Read the summary here:
Where would such an appevent be written for it to work in any excel file? It could be in a random (hidden) macro enabled workbook?

re: Could that be causing the slowness on your work machine?
I think not. Slowness is given by 2-3 local background running processes. Two are windows related and 3rd is KAV.

re: [A:A].TextToColumns Destination:=[A1], DataType:=xlDelimited, TrailingMinusNumbers:=True
Truly a nice find! Changed A:A and A1 to Selection
Times: 9:53:51 a.m. 9:53:51 a.m.

It's interesting what you can find when running a macro :)

The more interesting thing is that I tried the "old" code now, for the same number of entries (1200), and it worked "instantly" 9:54:44 a.m. 9:54:44 a.m.

So my guess remains that this slowness issue is actually windows/running applications based, and not excel related. The fact that both codes work "instantly" now is proof to that.

Thank you very much for both of your help. I don't even know which answer I should choose as best. :)


Report •

#22
May 25, 2018 at 03:52:53
I'd give it to Razor2.3. That is by far the most efficient method.

Anytime that you eliminate a For-Next loop you gain efficiency and elegance.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#23
May 25, 2018 at 12:03:37
Thanks to both of you for your help and time.

Report •

Ask Question