VBA copy LEFT function / formula down all cells in column

catflap_

New Member
Joined
Feb 16, 2021
Messages
4
Hi guys! Long time reader, first time poster.

Getting very frustrated with my VBA so any help is appreciated.

In my worksheet "Paste Data", I have a string of text in column G and need to use the LEFT formula to find and copy the text before the character " - " into column H.

My VBA code works, but only for the first row. When I try to use various VBA to copy this down to all the cells in column H, the results are all the same as the results in cell H2. Understandably because the code is always directing the formula back to cell G2.

How can I edit the code so that a) the results in cell H3 are dependent on G3, and H4 is dependent on G4 etc. and b) copy this down the column as needed?

Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row
For Each c In Range("H2:H" & Lastrow)
c.Value = Left(Worksheets("Paste Data").Range("G2"), (Application.WorksheetFunction.Find(" - ", Worksheets("Paste Data").Range("G2"), 1) - 1))
Next


Thanks for your help!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,647
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub catflap()
   With Sheets("Paste Data")
      With .Range("H2:H" & .Range("G" & Rows.Count).End(xlUp).Row)
         .Value = .Worksheet.Evaluate(Replace("if(isnumber(find(""-"",@)),left(@,find(""-"",@)-1),"""")", "@", .Offset(, -1).Address))
      End With
   End With
End Sub
 
Solution

catflap_

New Member
Joined
Feb 16, 2021
Messages
4
Hi @Fluff - this seems to work perfectly except for the last 10 rows, where it defaults back to the results in row 2! Very strange!

Any clue why that might be the case?

Many thanks for your help on this!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,647
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data showing the problem?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also what version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

catflap_

New Member
Joined
Feb 16, 2021
Messages
4
All good - must have been a glitch in the matrix - I opened a new excel and tried the code again and it worked perfectly.

Thanks so much @Fluff !
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,647
Office Version
  1. 365
Platform
  1. Windows
Glad it's working & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top