Change values after VLookup

[rollingzep]

Hi All,

I have a excel sheet on which I have a column with data filled after a Vlookup.
Among the values that come in, I have a Column BE with value "NR", which is a valid value.

I need to change it to "N" in the same column, BE

Set ws = wbexcel.Sheets("TSS Trans")

lrow = ws.Range("AG" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

is not working

 

[Coyotex3]

Hi All,

I have a excel sheet on which I have a column with data filled after a Vlookup.
Among the values that come in, I have a Column BE with value "NR", which is a valid value.

I need to change it to "N" in the same column, BE

Set ws = wbexcel.Sheets("TSS Trans")

lrow = ws.Range("AG" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

is not working

First thing I can think of is that you have

lrow=  ws.Range("AG" & Rows.Count).End(xlUp).Row

But here you have

    For i = 2 To LastRow

Should it be

 For i = 2 To Lrow

perhaps?

 

[rollingzep]

First thing I can think of is that you have

lrow=  ws.Range("AG" & Rows.Count).End(xlUp).Row

But here you have

    For i = 2 To LastRow

Should it be

 For i = 2 To Lrow

perhaps?

My bad..

I changed it to lrow and get "Type mismatch" error on the Bold line

lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To LastRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

 

[Coyotex3]

My bad..

I changed it to lrow and get "Type mismatch" error on the Bold line

lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To LastRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

When you say you changed it to lrow do you mean this:

lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

If so, did that give you a Type mismatch error?

 

[rollingzep]

When you say you changed it to lrow do you mean this:

lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lRow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

If so, did that give you a Type mismatch error?

I did change it to lrow but got the Type mismatch error

<code>
lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lrow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

</code>

 

[Coyotex3]

I did change it to lrow but got the Type mismatch error

<code>
lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lrow
If ws.Range("BE" & i).Value = "NR" Then
ws.Range("BE" & i).Value = "N"
End If
Next i

</code>

Give this a try

Sub Rollingzep()

Dim lrow As Long
Dim i As Long

lrow = Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lrow
If Range("BE" & i).Value = "NR" Then Range("BE" & i).Value = "N"
Next i

End Sub

 

[rollingzep]

Give this a try

Sub Rollingzep()

Dim lrow As Long
Dim i As Long

lrow = Cells(Rows.Count, "AG").End(xlUp).Row

For i = 2 To lrow
If Range("BE" & i).Value = "NR" Then Range("BE" & i).Value = "N"
Next i

End Sub

I tried and I did not get that error but the values did not change

 

[Coyotex3]

I tried and I did not get that error but the values did not change

The code works for me, and it changed the "NR" on my worksheet to "N". are you able to post a sample of your data?

 

[rollingzep]

I tried and I did not get that error but the values did not change

The code works for me, and it changed the "NR" on my worksheet to "N". are you able to post a sample of your data?

The above is for TSS Trans

<code>

'Read HQLA_FR2052A_ELIG_AST_CL_DESGTN from HQLA's sheet

Set ws = wbexcel.Sheets("TSS Trans")

ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C3,3,0)"

'Change NR to N in BE

'lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row
lrow = Range("AG" & Rows.Count).End(xlUp).Row

For i = 2 To lrow
If Range("BE" & i).Value = "NR" Then
Range("BE" & i).Value = "N"
End If
Next i

</code>

 

[Coyotex3]

View attachment 74452

The above is for TSS Trans

<code>

'Read HQLA_FR2052A_ELIG_AST_CL_DESGTN from HQLA's sheet

Set ws = wbexcel.Sheets("TSS Trans")

ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C3,3,0)"

'Change NR to N in BE

'lrow = ws.Cells(Rows.Count, "AG").End(xlUp).Row
lrow = Range("AG" & Rows.Count).End(xlUp).Row

For i = 2 To lrow
If Range("BE" & i).Value = "NR" Then
Range("BE" & i).Value = "N"
End If
Next i

</code>

Hmmm, maybe the codes are not working because the value on “BE” is the result of a formula.

If you manually change the values on “BE” and typed “NR” does that do anything?