Decipher & possibly change an inherited macro

[sanantonio]

Hi All,

Have inherited the following macro. It basically loops through a list of numbers and sendkeys them all into a computer-specific data input screen.

Could anyone please help me with a translation? I've added my own where I can determine what's what.

Sub ALO_SendKeys()

Dim i As Long
Dim U As Long

For i = ActiveCell.Row To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
'Minimizes excel window
       Application.WindowState = xlMinimized 
       
'If blank return 0
 If Cells(i, ActiveCell.Column) = "" Then
    Application.SendKeys ("0")
      Else
      End If
       
       Application.SendKeys Cells(i, ActiveCell.Column), True
       Application.SendKeys "{DOWN}"


Next i

    Application.SendKeys "{NUMLOCK}"

End Sub

Not really sure how it works, I presume somewhere in there we're telling it to input what is in the cell, then the next etc.

It seems to only work when you have the correct cell highlighted to begin with. I presume this is dictated by this line of code:

For i = ActiveCell.Row To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Could someone help me out with the correct code for making this work regardless of what cell you have highlighted if the numbers always begin in D7 ?

Thanks y'all!

 

[Joe4]

To have it go from D7 down to the last row with data in column D, you can change that line to this:

For i = 7 To Cells(Rows.Count, "D").End(xlUp).Row

 

[sanantonio]

Hi, thanks for coming back to me so quickly!

That worked to mean it only copied from Row 7 but ignored the D completely.

I managed to fix this by running a Range("D7").Select in:

Sub FVM_Sender()

'Request the user to confirm they've checked everything is on status 9
Dim answer As Integer
answer = MsgBox("Have you checked all your items are in the same order as they appear in FVM?", vbQuestion + vbYesNo)

  If answer = vbYes Then

Dim i As Long
Dim U As Long

Range("D7").Select

For i = 7 To Cells(Rows.Count, "D").End(xlUp).Row
       Application.WindowState = xlMinimized
       
 If Cells(i, ActiveCell.Column) = "" Then
    Application.SendKeys ("0")
      Else
      End If
       
       Application.SendKeys Cells(i, ActiveCell.Column), True
       Application.SendKeys "{DOWN}"

Next i

    Application.SendKeys "{NUMLOCK}"

ActiveWindow.WindowState = xlMaximized

Else
    MsgBox "Please check that all of your items are in the correct order and try again"
  End If

End Sub

And it now works a treat!

 

[Joe4]

That worked to mean it only copied from Row 7 but ignored the D completely.

I managed to fix this by running a Range("D7").Select in:

That is not necessary. You just need to also replace all instances of:

ActiveCell.Column

with

"D"

So just update these two references:

Cells(i, ActiveCell.Column)

to this:

Cells(i, "D")

and you can get rid of that select statement.

 

[Joe4]

I updated the solution to my original reply.
While they both helped you solved your issue, the first one was geared more towards your original question. So that is probably the one that best answers the original qustion.