VBA code should auto run for next cell data [loop?]

[Mehmud Khan]

Dear friends,

Good day!


I have data from cell A1:A100 [list may be extend], and I have below codes which run in column C.

As of now if I keep cursor in C1 then run macros then VBA will auto run and provide data in cell C1.

I need your help to run the data in entire range, when C1 data is update then VBA move to C2 and update C2 then C3, C4 so on...

Sub ZarotaCheck()
ActiveCell.Clear
ActiveCell.Offset(0, -2).Copy
AppActivate ("Zarota"), 10
SendKeys ("+{F2}"), 10
SendKeys ("^v"), 10
SendKeys ("{ENTER}"), 10
SendKeys ("+{F2}"), 10
Application.Wait (Now + TimeValue("00:00:01"))
ActiveCell.Offset(0, -1).Copy
SendKeys ("^v"), 10
SendKeys ("{ENTER}"), 10
SendKeys ("^a"), 10
SendKeys ("^c"), 10
Application.Wait (Now + TimeValue("00:00:01"))
ClipboardToCell ActiveCell
End Sub

I hope, I am able to explain my query.

Lots of hopes from this team.

Thanks in advance.
Regards,

 

[rollis13]

No way to test since the two functions aren't available so have a try on a copy of your sheet.

Option Explicit
Sub ZarotaCheck()
    Dim x     As Long
    For x = 1 To Range("A" & Rows.Count).End(xlUp).Row 'from row 1 to last row of column A)
        Cells(x, "C").Clear
        Cells(x, "C").Offset(0, -2).Copy
        AppActivate ("Zarota"), 10
        SendKeys ("+{F2}"), 10
        SendKeys ("^v"), 10
        SendKeys ("{ENTER}"), 10
        SendKeys ("+{F2}"), 10
        Application.Wait (Now + TimeValue("00:00:01"))
        Cells(x, "C").Offset(0, -1).Copy
        SendKeys ("^v"), 10
        SendKeys ("{ENTER}"), 10
        SendKeys ("^a"), 10
        SendKeys ("^c"), 10
        Application.Wait (Now + TimeValue("00:00:01"))
        ClipboardToCell Cells(x, "C")
    Next x
End Sub

 

[Mehmud Khan]

Dear rollis,

Thanks for reviewing my issues.

Your provided VBA is works for below row coding;

>>> use code - tags <<<

Cells(x, "C").Clear

I mean its only clear the cell and not run the other line commands.

So, after few researches, I found below which works as I was looking for;

>>> use code - tags <<<

Dim i As Long
i = 2
Do Until IsEmpty(Cells(i, 1))
      
        Application.Wait (Now + TimeValue("00:00:02"))
        ActiveCell.Clear
        ActiveCell.Offset(0, -2).Copy
        AppActivate ("Zarota"), 100
        SendKeys ("+{F2}"), 100
        SendKeys ("^v"), 100
        SendKeys ("{ENTER}"), 100
        SendKeys ("+{F2}"), 100
        Application.Wait (Now + TimeValue("00:00:02"))
        ActiveCell.Offset(0, -1).Copy
        SendKeys ("^v"), 100
        SendKeys ("{ENTER}"), 100
        SendKeys ("^a"), 100
        SendKeys ("^c"), 100
        Application.Wait (Now + TimeValue("00:00:02"))
        ClipboardToCell ActiveCell

i = i + 1
Loop

  
End Sub

But still in above code there is one issue, after finishing each round (when loop restart to next cell) I need to toggle back to excel OR click back to excel then only the loop start ELSE macros loop is not starting again. I tried "window.activate" but that is not working.


I hope, I was able to explain above.



Thanks and Regards,
Mehmud

 

[rollis13]

Changing loop from a For/Next to Do Until/Loop will do exactly the same. Both move/loop from one cell to the next in column A.
So if you have issues you need to check what's going on in your Function (AppActivate); in the absence of a sample file, I can't be of any other help.

 

[Marc L]

My two cents according to crappy VBA functions :​

• do not use VBA SendKeys but Windows SendKeys via WScript ActiveX.
• Do not use VBA Wait but Windows Sleep method via a dll …

And according to your AppActivate & SendKeys codelines : ", 100" as no sense except True as you can read within VBA help …​

​

 

[Mehmud Khan]

Dear Rollis and Marc,


Good day!

As I don't have even good knowledge in excel VBA, so for me it's really very difficult task to write everything in correct format/procedure.

All the coded mentioned above by me are stolen from google in multiple pcs...

Guys, I belongs to finance and accounting department, so the codes mentioned above is already huge achievement for me...


Now below codes are totally working good for me...

[highly appreciable, if you convert some formal changes in correct VBA format]




>>> You have noted many times <<<
>>> use code - tags <<<

Option Explicit

Sub ZarotaCheck()


Dim i As Long
i = 2
Do Until IsEmpty(Cells(i, 1))
       
        Application.Wait (Now + TimeValue("00:00:02"))
        ActiveCell.Clear
        ActiveCell.Offset(0, -2).Copy
        AppActivate ("Zorato"), 100
        SendKeys ("+{F2}"), 100
        SendKeys ("^v"), 100
        SendKeys ("{ENTER}"), 100
        SendKeys ("+{F2}"), 100
        Application.Wait (Now + TimeValue("00:00:02"))
        ActiveCell.Offset(0, -1).Copy
        SendKeys ("^v"), 100
        SendKeys ("{ENTER}"), 100
        SendKeys ("^a"), 100
        SendKeys ("^c"), 100
        Application.Wait (Now + TimeValue("00:00:02"))
        ClipboardToCell ActiveCell
                 
 
       
       
       
ActiveCell.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
   
            ActiveCell.Offset(1, 0).Select
            Application.Wait (Now + TimeValue("00:00:02"))
       
           Application.SendKeys ("%{TAB}"), 100
            DoEvents
          
           Application.Wait (Now + TimeValue("00:00:01"))

i = i + 1
Loop
 
   
End Sub

always thanksful for Chandoo plateform....


as always regards,
Mehmud