VBA to update cell value based on another cell

[olympiac]

I need to update the values in column A based on the value in column B.
Here is the code:

If Range("A2").Value = "refrin" Then
Range("B2").Value = "RefrinDHP"

Else

End If
If Range("A3").Value = "refrin" Then
Range("B3").Value = "RefrinDHP"
etc......

I need to continue it until the end of the column.
Do I need to use a loop? Can you help please?

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "refrin" Then
            Range("B" & i).Value = "RefrinDHP"
        End If
    Next i
End Sub

 

[olympiac]

It works perfectly.

Many thanks for your answer

 

[MeetGlen]

Try:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "refrin" Then
            Range("B" & i).Value = "RefrinDHP"
        End If
    Next i
End Sub

Thank you. Please could you help if a cell appears blank but has space in it, for example i am entering this in B4, =IF(LEN(A4)>2,"Cell has value","")

 

[Andrew Poulsom]

Thank you. Please could you help if a cell appears blank but has space in it, for example i am entering this in B4, =IF(LEN(A4)>2,"Cell has value","")

Please start your own thread rather than hijacking one that seems to be unrelated.

 

[MeetGlen]

Please start your own thread rather than hijacking one that seems to be unrelated.

Sorry sir, i did not mean to hijack. i though i would able to explain it better in this way, since the code works as expected based on this original thread, and it worked fine as i my case when i replaced the keyword 'refrin' to "", and just then i realised there should be some way to check if the cell is really blank, as sometimes there is space and we are not knowing... Thank you.

 

[pdenna1]

What if instead of plain text "RefrinDHP" (Range("B" & i).Value = "RefrinDHP") I wanted to input a formula referencing another column, ex. =workday("b" & i, "c" & i)?


If range("b" & i).value = "refrin" then
range("a" & i).value = formulaR1C1 = "workday("b" & i, "c" & i)"

 

[Andrew Poulsom]

How can you use "refrin" in a formula that uses the WORKDAY function?

 

[pdenna1]

I was using the example already given, but my situation is obviously different. I do realize that I used the text field in the workday formula, it should've referenced another date or number field.
Basically I need a cell updated (workday formula) if it meets a certain value (text, "refrin" or in my case "Calendar") from a picklist field of another cell along the same row. Example below.


[TABLE="width: 559"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=workday(b1,c1)[/TD]
[TD]12/31/14[/TD]
[TD]3[/TD]
[TD]Calendar[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=workday(b2,c3)[/TD]
[TD]12/31/15[/TD]
[TD]4[/TD]
[TD]Fiscal[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=workday(b3,c3)[/TD]
[TD]12/31/16[/TD]
[TD]-5[/TD]
[TD]Calendar[/TD]
[/TR]
</tbody>[/TABLE]

The code would be something like this:
I believe FormulaR1C1 has to be a child to a range, but not sure on the syntax for it. Cells a1, a2, a3, etc. would be updated with the workday formula if d1, d2, d3, etc. = "Calendar"

Sub Test() Dim LastRow As Long Dim i As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow If Range("d" & i).Value = "Calendar" Then Range("a" & i).Value = Range("a" & i).FormulaR1C1 "= WORKDAY(RC[Range("b" & i],RC[Range("c" & i)])" End If Next iEnd Sub

 

[Andrew Poulsom]

That would be:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("d" & i).Value = "Calendar" Then
            Range("a" & i).FormulaR1C1 = "= WORKDAY(RC2,RC3)"
        End If
    Next i
End Sub