0

enter image description here

I need to update (X,Y) coordinates in a program called BlueZone using VBA based on a 6 digit location ID.

The three variables (1) location (2) X (3) Y are all on an spreadsheet.

I want my code to loop through each location ID (column A) and get the corresponding X and Y coordinates (columns B, C respectively). IOW, my cell A2 = location ID, B2 = X coordinate, C3 = Y coordinate.

Sub FiXY()

'bzhao is the object declaration to manipulate emulation software by BlueZone

Dim bzhao As Object
Set bzhao = CreateObject("BZWhll.WhllObj")
bzhao.Connect ""

Dim myLoc As Variant


myRange = ActiveSheet.Range("A2:C1000") 'have also tried "A2:A1000"

For Each myLoc In myRange

'end loop at blank cell
    If myLoc = "" Then
        Exit For
            End If

'Query location

bzhao.SendKey "Q"
bzhao.Wait 0.2
bzhao.SendKey myLoc 'passes just fine
bzhao.Wait 0.2
bzhao.SendKey "<enter>"
bzhao.Wait 0.2

'does not work
bzhao.SendKey myLoc.Offset(0,1).Value
bzhao.SendKey myLoc.Offset(0,2).Value

'does not work
bzhao.SendKey myRange.Offset(0,1).Value
bzhao.SendKey myRange.Offset(0,2).Value

Next myLoc

End Sub

I understand that SendKey is not ideal, but this loop has worked flawlessly for me with a static X or Y that I can call from a cell on the sheet, so I believe it the structure of my code is the issue.

How do I get the three variables into my loop?

AKdelBosque
  • 95
  • 15
  • The combination that works is `ActiveSheet.Range("A2:A1000")` and `myLoc.Offset(0,1).Value` / `myLoc.Offset(0,2).Value`, but `myRange` must be `As Range` and you must [use `Set`](https://stackoverflow.com/a/17877644/11683) to assign it. – GSerg Dec 18 '19 at 00:55
  • @gserg Amazing - I can't believe it was that simple. Thank you so much! – AKdelBosque Dec 18 '19 at 01:22

0 Answers0