Replacing a long integer with text in VBA

[Teeks2k]

Hello,

This is my first post but I do frequent these forums often trying to figure out how to fix my VBA codes, but I am still struggling with my current issue.

Here is what I am trying to do:

I am comparing 2 worksheets to see if Product A is on Sheet B, and I have about 80,000 lines to go through, so in Excel I use =IF(ISNA(VLookup(arg1,arg2,arg3,arg4)),"N","Y"), this will return a simple yes it is on the sheet, or a no it is not.

I've been trying to do this in VBA and am having some problems, so first I created a loop to do exactly what I did before

Sub Columnformatting1()
'// Convert Long Integer to Y
 
For i = 1 To Range("D200000").End(xlUp).Row - 1
    Range("G" & i + 1).Value = WorksheetFunction.If(WorksheetFunction.IsNA(WorksheetFunction.VLookup(Range("D" & i + 1).Value, Range("Vaccines")), 1, 0), "N", "Y")
    Range("Y" & i + 1).Value = WorksheetFunction.If(WorksheetFunction.IsNA(WorksheetFunction.VLookup(Range("D" & i + 1).Value, Range("EXDS")), 1, 0), "N", "Y")
    
Next
 
Resume Next
End Sub

This didn't work because it didn't know what to do when the product was not found so I got a Vlookup error, I added an "On Error GoTo" command and the only thing I get is an "N", which I know to be incorrect.

I was able to get Vlookup to work, see the screenshot here, Column D is the lookup value, Column G and Y are the results. What I am trying to do from here is to convert all the on numbers in either column G or Y to text "Y" and I am not sure how to do this...I tried the following IF - THEN code but it didn't change anything.

Sub Columnformatting1()
'// Convert Long Integer to Y

On Error GoTo NotFound:

For i = 1 To Range("D200000").End(xlUp).Row - 1

    Range("G" & i + 1).Value = WorksheetFunction.VLookup(Range("D" & i + 1).Value, Range("Vaccines"), 1, 0)
    Range("Y" & i + 1).Value = WorksheetFunction.VLookup(Range("D" & i + 1).Value, Range("EXDS"), 1, 0)

Next

NotFound:

    Range("G" & i + 1).Value = "N"
    Range("Y" & i + 1).Value = "N"

Resume Next

If Range("G" & i + 1).Value = "N" Then
    Range("G" & i + 1).Value = "N"
        Else
            Range("G" & i + 1).Value = "Y"
End If

If Range("Y" & i + 1).Value = "N" Then _
    Range("Y" & i + 1).Value = "N"
        Else
            Range("Y" & i + 1).Value = "Y"
End If

End Sub

I also tried to code the IF portion of the code like this, which also did nothing

If Range("G" & i + 1).Value <> "N" Then
    Range("G" & i + 1).Value = "Y"
End If

If Range("Y" & i + 1).Value <> "N" Then _
    Range("Y" & i + 1).Value = "Y"
End If

This seems like it would such a simple thing to do but I am having the hardest time converting these numbers to a simple Y. Any help would be appreciated,

Thanks

 

[MrKowz]

Welcome to the forums!

We should be able to take care of this rather easily with the .Find method. If I understand correctly, you just simply want to return a Y/N in column G if the value is/isn't found in the named range "Vaccines", and you want to return a Y/N in column Y if the value is/isn't found in the named range "EXDS".

Give this a shot:

Public Sub Teeks2k()
Dim rng     As Range, _
    tmp     As String, _
    LR      As Long, _
    i       As Long
    
Dim sWS     As Worksheet
    
Set sWS = ActiveSheet

LR = sWS.Range("D" & Rows.Count).End(xlUp).Row

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

For i = 1 To LR
    tmp = sWS.Range("D" & i).Value
    With Range("Vaccines")
        Set rng = .Find(tmp, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            'value was found
            sWS.Range("G" & i).Value = "Y"
        Else
            'value was not found
            sWS.Range("G" & i).Value = "N"
        End If
    End With
    Set rng = Nothing
    With Range("EXDS")
        Set rng = .Find(tmp, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            'Value was found
            sWS.Range("Y" & i).Value = "Y"
        Else
            'Value was not found
            sWS.Range("Y" & i).Value = "N"
        End If
    End With
    Set rng = Nothing
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[Teeks2k]

I had to add a +1 to the range because this changed my headers, but other then that this works perfect

Thank you!

 

[Teeks2k]

This .find method is essential a VLookup right? Is there a benefit to doing it this way vs the way I did it before? Other than being able to specify the value that you want to return...

 

[MrKowz]

The .Find method is using the same process as using CTRL+F. It is beneficial to do it this way, as it has very little overhead calculation (meaning it calculates and processes significantly faster). There is also a host of things you can do with the .Find method that you cannot do with the VLOOKUP, because the Find method returns a range object, and VLOOKUP returns a value. With the range object, we're able to do things in relation to the found cell. In this case, we're only checking to see if a cell was found. In other cases, we could, for example, change the formatting of the found cell, adjust the value or a cell that is in relation to the found cell (i.e. do something to the cell 2 columns to the right with rng.offset(0,2)).

Hope that makes sense. Glad I was able to help find a working solution for you, and thanks for the feedback!

 

[Rick Rothstein]

...and I have about 80,000 lines to go through

Just out of curiosity, what range has the 80,000 "lines" (rows?) in it... Vaccines, EXDS or the values in Column D?

 

[Teeks2k]

The values in column D, the Vaccine and EXDS lists are relatively small, 20 lines or so each.

 

[Rick Rothstein]

The values in column D, the Vaccine and EXDS lists are relatively small, 20 lines or so each.

What did you mean by "and I have about 80,000 lines to go through" which you wrote in your original message? And where does it figure into what you are asking us for?