IF THEN VBA MACRO - Update one column if contents of another = 100%

Question

I have a workbook with "Results" being sheet 3, this being the worksheet I want to use.

I have tried a few formulaes to try and add a macro to do the following:

I have column G with percentages. I then have column I where I would like there to be a result saying TRUE/FALSE where the contents of G are equal to 100%. Column G is formatted to percentage with two decimals.

Some considerations: I have my first row being a Hyperlink to another sheet, then my headings, then the first row of "results". I have 457 rows, if there is a measurement of the range, perhaps it could be on A?

I keep getting this error 9 with my range and have got a bit stuck.

Thanks in advance!

Sub PartialHits1() 
Dim rng As Range 
Dim lastRow As Long 
Dim cell As Range 

With Sheet3 
    lastRow = .Range("G" & .Rows.Count).End(xlUp).Row 
    Set rng = .Range("G1:G" & lastRow) 
    For Each cell In rng 
        If cell.Value = 100 
        Then 
            cell.Range("I1:I1").Value = 100 
        End If 
    Next 
End With 

End Sub

(I have hacked this a bit, just was trying to get it to set as 100 instead of the TRUE/FALSE Also was playing around near the Sheet 3 part as I got errors.)

Replace cell.Range("I1:I1").Value = 100 by Range("I" & cell.Row).Value = "True" Is that what you are trying? Also you don't need VBA for this. You can achieve it using formulas as well. One more thing. Please indent your code nicely so that it is easier to read — Siddharth Rout
Thanks for the response. Why I am using VBA is because this is going to be step 1 of about 3 that I'd like to be one button every time. Once this has set the 100% values I want it to export those (Easy enough and has worked). Then all remaining ones I will be grouping by Borders, seeing if any of that group had a 100% hit, excluding the whole grouping, and exporting all remaining groups into a new sheet. That is my end goal. — user4242750
Ok. Did you try what I suggested? You will also have to change If cell.Value = 100 to If cell.Value = 1 as Mate suggested? — Siddharth Rout
@SiddharthRout, and Mate, it is working. Thank you very much for your help. — user4242750

Máté Juhász Máté Juhász · Accepted Answer · 2015-06-14T07:41:04

RangeVariable.Range can refer only to a cell within RangeVariable, so you can't refer to column I in this way. Try: .Range("I"&cell.row)=100.

Also your criteria is probably wrong, if you have 100% in a cell it's actual value is 1.

And last question: why do you want to do this with VBA, it would be much more simple with worksheet function =IF(G3=1,100,"")

IF THEN VBA MACRO - Update one column if contents of another = 100%

1 Answers