Using VBA with paste special to multiply numerical values in range only if cell value in first column meets criteria

[Storm8585]

Hi all,

I am struggling this. I have a worksheet with lab data in that is imported into my workbook. From row 4 downwards each line of the workbook contains lab data: In column C the units of the tests are given -either mg/kg or ug/kg. From column D across to AD each cell will contain lab data: this is shown as either a number, "-" or the "<" symbol preceding a number. Each row contains a different test to row 200 and potentially different units (either ug/kg or mg/kg).

So I am trying to create some VBA that looks in a set range, and for the first row in that range, check to see if "ug/kg" is in column C. If it is it will multiply all number values in that row by 1000 and ignore the "-" and "<" cells. Then it will move on to the next row and on. If "mg/kg" is present it moves on to the next row.
Any suggestions?

 

[SteveO59L]

something along the lines of

Dim c1 As Range, rng1
Dim c2 As Range, rng2

Set rng1 = Range("c4:c200")
For Each c1 In rng1
If c1.Value = "ug/kg" Then
Set rng2 = Range("d" & c1.Row & ":AD" & c1.Row)
For Each c2 In rng2
If IsNumeric(c2.Value) Then
c2.Value = c2.Value * 1000
End If
Next c2
End If
Next c1

 

[Storm8585]

something along the lines of

Dim c1 As Range, rng1
Dim c2 As Range, rng2

Set rng1 = Range("c4:c200")
For Each c1 In rng1
If c1.Value = "ug/kg" Then
Set rng2 = Range("d" & c1.Row & ":AD" & c1.Row)
For Each c2 In rng2
If IsNumeric(c2.Value) Then
c2.Value = c2.Value * 1000
End If
Next c2
End If
Next c1

Thanks so much. Worked like a treat!