PDA

View Full Version : [SOLVED] VBA formula help



Emoncada
09-15-2015, 11:45 AM
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
09-15-2015, 01:46 PM
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
09-15-2015, 01:58 PM
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
09-15-2015, 03:19 PM
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
09-15-2015, 04:53 PM
Yes same result

Emoncada
09-16-2015, 05:26 AM
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.