Compile error

[jyokom]

Why do I get a compile error: Invalid Qualifier when I run the following? The debugger highlights the "i" in the line Cbo_ARTrack & i.Value

Sub SaveAR_Loop()
Dim i As Integer
Dim NextRow As Integer
NextRow = Worksheets("Saved").Range("A65536").End(xlUp).Row + 1
For i = 1 To 8
Worksheets("Saved").Cells(NextRow, 14 + i).Value = "Cbo_ARTrack" & i.Value
Worksheets("Saved").Cells(NextRow, 15 + i).Value = "DTP_ARCT" & i.Value
Worksheets("Saved").Cells(NextRow, 16 + i).Value = "TB_TrkTime" & i.Value
Worksheets("Saved").Cells(NextRow, 17 + i).Value = "Cbo_RcvrUnit1_" & i.Value
Worksheets("Saved").Cells(NextRow, 18 + i).Value = "Cbo_RcvrMDS1_" & i.Value
Worksheets("Saved").Cells(NextRow, 19 + i).Value = "TB_RcvrCall1_" & i.Value
Worksheets("Saved").Cells(NextRow, 20 + i).Value = "TB_OffOn1_" & i.Value

Next i

ThisWorkbook.Save

End Sub

 

[Jonmo1]

You use .Value on a Range or other objects, i is just a number...

Change all the
i.Value
to just
i

hope that helps.

 

[mouse88]

You need to take the .Value of from the i.

Worksheets("Saved").Cells(NextRow, 14 + i).Value = Cbo_ARTrack & i

You just use i on its own as it is an integer value.

 

[jyokom]

Actually each of those are controls. and aren't returning the values of the controls. The controls are named for example Cbo_ARTrack1 & Cbo_ARTrack2, etc.

 

[jyokom]

You use .Value on a Range or other objects, i is just a number...

Change all the
i.Value
to just
i

hope that helps.

How do I get it to return the value of the control without using .Value?

 

[Jonmo1]

Unfortunately, it's much more difficult to loop through controls than you would think...

Standby...

 

[Jonmo1]

See if this gets you started...

Sub testing()
Dim c As OLEObject, x As Long, NextRow As Long
NextRow = Worksheets("Saved").Range("A65536").End(xlUp).Row + 1
For Each c In Sheets("Saved").OLEObjects
    Select Case Left(c.Name, Len(c.Name) - 1)
        Case "Cbo_ARTrack": x = 14
        Case "DTP_ARCT": x = 15
        Case "TB_TrkTime": x = 16
        Case "Cbo_RcvrUnit1_": x = 17
        Case "Cbo_RcvrMDS1_": x = 18
        Case "TB_RcvrCall1_": x = 19
        Case "TB_OffOn1_": x = 20
        Case "TextBox": x = 21
    End Select
    Worksheets("Saved").Cells(NextRow, x + Right(c.Name, 1)).Value = c.Object
Next c
End Sub

 

[Jonmo1]

Wait, this might be better and easier to follow/modify

Sub testing()
Dim i As Long, NextRow As Long
NextRow = Worksheets("Saved").Range("A65536").End(xlUp).Row + 1
With Sheets("Sheet1")
    For i = 1 To 8
        .Cells(NextRow, 14 + i).Value = .OLEObjects("Cbo_ARTrack" & i).Value
        .Cells(NextRow, 15 + i).Value = .OLEObjects("DTP_ARCT" & i).Value
        .Cells(NextRow, 16 + i).Value = .OLEObjects("TB_TrkTime" & i).Value
        .Cells(NextRow, 17 + i).Value = .OLEObjects("Cbo_RcvrUnit1_" & i).Value
        .Cells(NextRow, 18 + i).Value = .OLEObjects("Cbo_RcvrMDS1_" & i).Value
        .Cells(NextRow, 19 + i).Value = .OLEObjects("TB_RcvrCall1_" & i).Value
        .Cells(NextRow, 20 + i).Value = .OLEObjects("TB_OffOn1_" & i).Value
    Next i
End With
ThisWorkbook.Save
End Sub

 

[jyokom]

I get a compile error: Invalid or unqualified reference with the first .OLEObjects selected

 

[Jonmo1]

In the VBA window, go to tools - references
Make sure you have the following checked..

Visual Basic For Applications
Microsoft Excel xx.x Object Library
OLE Automation
Microsoft Office xx.x Object Library

Save/Close/Reopen.