VBA formula help

[Emoncada]

I have the following code

Dim Track As String

    For x = 2 To Lr
        
        Track = ws.Cells(x, "B")
        
        If ws.Cells(x, "A") <> "" Then
            ws.Cells(x, "D") = Application.WorksheetFunction.VLookup(ws.Cells(x, "A"), wsEmail.Range("A2:E" & LrEmail), 4, False)
            ws.Cells(x, "E") = Application.WorksheetFunction.VLookup(ws.Cells(x, "A"), wsEmail.Range("A2:E" & LrEmail), 5, False)
            ws.Cells(x, "F") = "=ShipTrack(" & Track & ",""UPS"")"
            
        shipD = ws.Cells(x, "C")
            Days = DateDiff("d", shipD, nowD)
            
            ws.Cells(x, "G") = Days
        End If
    Next x

I keep getting a runtime error and I was able to figure out that the problem is ws.cells(x, "B") always starts with a "1", but if I enter a letter infront of it this code works fine.
How can I have it get that cell value even when it starts with a number?

[Simon Lloyd]

Can you supply the entire macro? as it stands your code will bug out when it hits the first ws.Cells as you haven't declared what ws is, remove ws. and you'll find the code will progress further.

[Emoncada]

Sorry about that

Sub UpdateSh()
    Dim ws As Worksheet
    Dim wsEmail As Worksheet
    Dim shipD As Date
    Dim nowD As Date
    Dim Days As Integer
    Dim Track As String
    
    Set ws = Sheets("MV")
    Set wsEmail = Sheets("Emails")
    nowD = Date
    Lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    LrEmail = wsEmail.Cells(Rows.Count, "A").End(xlUp).Row
    For x = 2 To Lr
        
        Track = ws.Cells(x, "B")
        
        If ws.Cells(x, "A") <> "" Then
            ws.Cells(x, "D") = Application.WorksheetFunction.VLookup(ws.Cells(x, "A"), wsEmail.Range("A2:E" & LrEmail), 4, False)
            ws.Cells(x, "E") = Application.WorksheetFunction.VLookup(ws.Cells(x, "A"), wsEmail.Range("A2:E" & LrEmail), 5, False)
            ws.Cells(x, "F") = "=ShipTrack(" & Track & ",""UPS"")"
            
            shipD = ws.Cells(x, "C")
            Days = DateDiff("d", shipD, nowD)
            
            ws.Cells(x, "G") = Days
        End If
    Next x

End Sub

[SamT]

but if I enter a letter infront of it this code works fine.
How can I have it get that cell value even when it starts with a number?

Have you tried

Track = ws.Cells(x, "B").Text

[Emoncada]

Yes same result

[Emoncada]

After testing different ways I was able to get it to work with

ws.Cells(x, "F") = "=ShipTrack(B" & x & ",""UPS"")"

For some reason it didn't like the other setup.