Macro to search range for a portion of a specific value and if findit - then paste formula in anothe

J

JenIT

The below formula works as written for a set value of "66-1007" in column B.. My problem is I need to search for a portion of that value as the 66-10 will remain constant, but the last 2 numbers might change (it is always a 7character field). I have tried the * and # variables and have not had luck. Any help would be appreciated.

With Sheets("myworkshet")
Set rngToSearch = .Range(.Range("B2"), _
.Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToSearch
With rng
If .Value = "66-1007" Then
Cells(.Row, "K").FormulaR1C1 = "=(RC[6]+RC[7])*0.08615"
End If
End With
Next rng
 
R

Ron Rosenfeld

The below formula works as written for a set value of "66-1007" in column B. My problem is I need to search for a portion of that value as the 66-10 will remain constant, but the last 2 numbers might change (it is always a 7 character field). I have tried the * and # variables and have not had luck. Any help would be appreciated.

With Sheets("myworkshet")
Set rngToSearch = .Range(.Range("B2"), _
.Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToSearch
With rng
If .Value = "66-1007" Then
Cells(.Row, "K").FormulaR1C1 = "=(RC[6]+RC[7])*0.08615"
End If
End With
Next rng

Look at help for Like:

Try: if .value like "66-10##" then
 
J

JenIT

Perfect!!!! Thanks - amazing how one little word makes a world of difference. Your help is much appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top