VBA copy LEFT function / formula down all cells in column

[catflap_]

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!

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[catflap_]

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]

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_]

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]

Glad it's working & thanks for the feedback.