Excel VBA - Compile Err- Wrong number of arguments or invalid property assignment

Question

I'm trying to code an excel button to reference a value in Column AX Row 2, enter that reference into a field in our business system(IBM), and if it finds a trim at the coordinates matching the trim from Column AX Row 2, to enter that value into the same sheet in column F row 2, then move to the next row and repeat until the row is blank.

I've tried changing to For Next and adjusting code but can't figure out where I am stuck

Dim HE As Object
Set HE = CreateObject("BZWhll.WhllObj") 'BlueZone

Dim ExcelTaxID As String
Dim IBMTaxID As String
Dim IBMPvd As String
Dim EachRow As Integer

    AppActivate UseIBM1
    EachRow = 2
    Do While EachRow <> ""

        ExcelTaxID = Trim(ActiveWorkbook.Worksheet("ShrPntTransfer").Column("AX").row("EachRow").Value)

        HE.CurrentHost.PutText "BPIQ", 1, 1
        Utilities.PressKey "ENTER", False, UseIBM1, "01", , "YES", "BUTTONS"
        HE.CurrentHost.PutText "5", 22, 12
        Utilities.PressKey "ENTER", False, UseIBM1, "02", , "YES", "BUTTONS"
        HE.CurrentHost.PutText ExcelTaxID, 9, 2
        Utilities.PressKey "ENTER", False, UseIBM1, "03", , "YES", "BUTTONS"

        IBMTaxID = Trim(UseIBM1, 7, 8, 9)

            If ExcelTaxID = IBMTaxID Then
                IBMPvd = Trim(UseIBM1, 7, 35, 6)
                Sheets("ShrPntTransfer").Column("F").row("EachRow").cell.Value = IBMPvd
                ActionEntry = ActionEntry & "Provider Number Recorded"
            Else
                Sheets("ShrPntTransfer").Column("F").row("EachRow").cell.Value = "Provider Not Found"
            End If

            Utilities.PressTwoKeys "SHIFT", "F12", True, UseIBM2, "03", , "YES", "BUTTONS"

        EachRow = EachRow + 1
    Loop

Compile Error: wrong number of arguments or invalid property assignment

.Column("F").row("EachRow") >> .Columns("F").Rows(EachRow) — Tim Williams
– Tim Williams, Commented Aug 19, 2019 at 22:49

Scott Craner · Accepted Answer · 2019-08-19 22:54:19Z

2

one could simplify the reference from column(x).row(y) to use either with Range() or Cells() and EachRow is a variable and should be unquoted:

So:

Trim(ActiveWorkbook.Worksheet("ShrPntTransfer").Column("AX").row("EachRow").Value)

Becomes

Trim(ActiveWorkbook.Worksheets("ShrPntTransfer").Cells(EachRow,"AX").Value)

And

Sheets("ShrPntTransfer").Column("F").row("EachRow").cell.Value = IBMPvd

Becomes

Worksheets("ShrPntTransfer").Cells(EachRow,"F").Value = IBMPvd

edited Aug 19, 2019 at 22:54

answered Aug 19, 2019 at 22:49

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Williams Over a year ago

It does work if you add the s and unquote the EachRow, but certainly not a common method.

Scott Craner Over a year ago

@TimWilliams really? Wow did not think it would.

Tim Williams Over a year ago

Well a Column is a Range and a Range has rows so... I did have to check though.

Scott Craner Over a year ago

I can see the logic, and "learnt me" my new thing today. cool.

Vityata Over a year ago

Congrats on the 100.000 :)

Collectives™ on Stack Overflow

Excel VBA - Compile Err- Wrong number of arguments or invalid property assignment

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related