I have a range of cells, e.g. g1-g1000 that contains two type of entries: xx.xx or xx.xxCR, where xx.xx are numbers.
I want to search the range of cells for xx.xxCR and when a cell is found, copy the contents to the adjacent cell minus the CR, then delete the value in the original cell.
Cells that contain xx.xx will not haven any thing done to them.
E.G. cell g5 contains 23.67CR; after running the algorithm, cell h5 contains 23.67 and g5 is empty.
Do this for the range of values in g0-g1000
Here is my attempt:
Dim i
For i = 1 To 30
If InStr(UCase(Cells(i, "G")), "CR") Then
MsgBox "The string 'CR' was found in cell " & Cells(i, "G").Address(0, 0)
' Copy the cell containing xx.xxCR to the adjacent cell
Range(Cells(i, "G")).Select ' Keep getting error 1004 at this line
Range(Cells(i, "G")).Copy
Range(Cells(i, "H")).Select
ActiveSheet.Paste
' Remove the CR from the adjacent cell e.g. "C", just leaving xx.xx
Cells(i, "H") = WorksheetFunction.Substitute(Cells(i, "H"), "CR", "")
'Now Remove the contents of the cell where CR was found - how do this?
End If
Next
I keep getting runtime error 1004 Application defined or object defined error at the line:Range(Cells(i, "G")).Select
Can someone spot the error of my ways?
Thank you!
-J
Bookmarks