'Continue a loop after criteria is met a specified number of times
This is my first question and I´ll try to be as detailed as possible.
I wrote a macro at my work that should read a column with employment numbers, run these against different conversations in (TN3270). From that, I will get data to be able to demonstrate the distribution of working hours towards different work tasks and related information.
My basic file works well but I want to compress the time required and shorten the code.
I will attach parts of the code and my question mainly applies to the part that during the comment:
"If I don´t run the code down under let say 7 times total it goes to the next number in ws.Range("A" & i)"
Problem:
I want the code to loop 7 times in the part i try to describe instead of have 7 identical "Else"
If I take away 6 of the 7 identical Else´s, it doesn´t (obviously) repeat itself.
How do I write it so it loops trough all active lines in the conversation I´m using in IBM pcomm TN3270 and don´t go to next number in the range mentioned above (ws.Range("A" & i)) until it has reached desired rows? I want it to read from "ls_rad = 12" to "ls_rad = 20".
I would really appreciate other comments on what my code looks like and what can be improved!
EDIT:
Tried to explain my problem better.
Regarding the code I stated that it was part of the code, not full the code.
Could you tell me what your definition of properly indentation is? Don´t read this as if I´m whining, it´s a question.
Sleep seems to work in this, and several more macros, at least when used with terminal emulator called TN3270 (as I wrote earlier).
EDIT 2: Found a solution
Embarrassingly easy now when I found out.
To anyone else who´ll encounter this problem, see below.
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim autECLPSObj As Object
Dim autECLConnList As Object
Dim autECLOIA As Object
Dim FieldElement As Object
Dim autECLSession As Object
Dim autECLPS As Object
Public OIA As Object
Sub BBLU_LOOP_GET()
Dim i As Long, y As Long, x As Long
Dim nr_rows As Long
Dim ao1 As Integer
Dim Year As Integer, Week As String, månad As Integer
Dim xl_rad As Integer, ls_rad As Integer
Dim arbomrBBLU As Variant, anst As Long, tid As Variant
Dim prest As Variant, stämpling As Variant, stämplingkolla As String
Dim tidstyp As String
Dim netto As Variant, ls_rad_netto As Long
Dim b As Integer, A As Integer, c As Integer, d As Integer
Dim StartTime As Double
Dim MinutesElapsed As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
StartTime = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'Ansluta till LS.
Set autECLSession = CreateObject("Pcomm.auteclsession")
Set autECLPS = CreateObject("Pcomm.auteclps")
Set autECLOIA = CreateObject("Pcomm.autecloia")
Set OIA = CreateObject("PCOMM.autECLOIA")
Set autECLConnList = CreateObject("PCOMM.autECLConnList")
autECLSession.SetConnectionByName ("B")
'Variabel worksheets i filen
Set ws = ThisWorkbook.Worksheets("XX_XXXXXXXXXXXX")
Set ws2 = ThisWorkbook.Worksheets("BBLU")
Set ws3 = ThisWorkbook.Worksheets("XXXXXX_XXXXXXX")
'Läs aktiv cell nerifrån -> upp
nr_rows = ws2.Range("G1048576").End(xlUp).Row
ws2.Range("G2:G31") = ws2.Range("G2:G31").Value2
i = 2
ao1 = ws2.Range("N3").Value
Year = ws3.Range("P2").Value
Week = ws3.Range("Q2").Value
månad = ws3.Range("Q2").Value
ls_rad = 12
ls_rad_månad = 10
ls_rad_netto = 11
ls_rad_netto_månad = 8
xl_rad = 2
Do Until ws.Cells(i, 1) = ""
'Ändra inte här!!!
If ws3.Range("Q1") = "Week" Then 'if Week
autECLSession.autECLPS.SendKeys ("WMND"), 23, 11
autECLSession.autECLPS.Wait 50
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys ("bblu"), 23, 11
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys ws.Range("A" & i), 4, 19
autECLSession.autECLPS.Wait 50
autECLSession.autECLPS.SendKeys [ao1], 6, 21
autECLSession.autECLPS.Wait 100
autECLSession.autECLPS.SendKeys "A", 7, 23
autECLSession.autECLPS.SendKeys [Year], 9, 20
autECLSession.autECLPS.SendKeys [Week], 10, 22
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys "[PF13]"
autECLSession.autECLPS.Wait 100
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
autECLSession.autECLPS.Wait 20
stämplingkolla = "A"
'Week 1
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
'"If I don´t run the code down under 7 times total
' it goes to the next number in ws.Range("A" & i)"
' when stämplingkolla = "A". I want it to read(loop?)
' 7 times and even if it gets "A" because there might be more than 1 "A".
Else
autECLSession.autECLPS.Wait 50
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 50
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
' Week 2
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
'Week 3
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
'Week 4
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
'Week 5
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
'Week 6
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
'Week 7
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
ls_rad = ls_rad + 1
Else
autECLSession.autECLPS.Wait 45
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("P2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("Q2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
autECLSession.autECLPS.Wait 45
ls_rad = ls_rad + 1
xl_rad = xl_rad + 1
End If
Else ' månad 'not active
autECLSession.autECLPS.SendKeys ("WMND"), 23, 11
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys ("bblu"), 23, 11
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys ws.Range("A" & i), 4, 19
autECLSession.autECLPS.SendKeys [ao1], 6, 21
autECLSession.autECLPS.SendKeys "A", 7, 23
autECLSession.autECLPS.SendKeys [Year], 9, 20
autECLSession.autECLPS.SendKeys [månad], 12, 22
autECLSession.autECLPS.Wait 175
autECLSession.autECLPS.SendKeys "[PF13]"
stämpling = autECLSession.autECLPS.GetText(ls_rad_månad, 14, 1)
stämplingkolla = "A"
autECLSession.autECLPS.Wait 45
End If
ls_rad = 12
ls_rad_månad = 10
i = i + 1
Loop
Call netto_BBAC
'ws2 = BBLU
b = ws2.Range("H1048576").End(xlUp).Row
For A = 2 To b
If ws2.Range("H" & A) = " " Then
ws2.Range("H" & A) = 0
End If
Next A
Call HÄMTA_TIDV
'ws = MA_Utlastningen
d = ws.Range("G1048576").End(xlUp).Row
For c = 2 To d
If ws.Range("G" & c) = " " Then
ws.Range("G" & c) = 0
End If
Next c
'Ändra cellernas värdetyp
Call ConvertTextToNumberLoop
'ws2 = BBLU
nr_rows = ws2.Range("A2").End(xlDown).Row
nr_rows = nr_rows + 1
ws2.Range(ws2.Cells(nr_rows, 7), ws2.Cells(600, 11)).ClearContents
Call ConvertTextToNumberLoop2
'ws = MA_Utlastningen
nr_rows = ws.Range("A2").End(xlDown).Row
nr_rows = nr_rows + 1
ws.Range(ws.Cells(nr_rows, 7), ws.Cells(200, 8)).ClearContents
'Round to #,##
Call RoundToTwo
'ws = MA_Utlastningen
nr_rows = ws.Range("A2").End(xlDown).Row
nr_rows = nr_rows + 1
ws.Range(ws.Cells(nr_rows, 7), ws.Cells(200, 8)).ClearContents
'Call RoundToTwo_2
'ws = MA_Utlastningen
nr_rows = ws.Range("H2").End(xlDown).Row
nr_rows = nr_rows + 1
ws.Range(ws.Cells(nr_rows, 8), ws.Cells(200, 7)).ClearContents
Call spara_2
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
End Sub
EDIT solution
Do Until ws.Cells(i, 1) = ""
If ws3.Range("O1") = "Week" Then 'if Week
autECLSession.autECLPS.SendKeys ("WMND"), 23, 11
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 100
autECLSession.autECLPS.SendKeys ("bblu"), 23, 11
autECLSession.autECLPS.SendKeys "[PF12]"
autECLSession.autECLPS.Wait 100
autECLSession.autECLPS.SendKeys ws.Range("A" & i), 4, 19
autECLSession.autECLPS.SendKeys [ao1], 6, 21
autECLSession.autECLPS.SendKeys "A", 7, 23
autECLSession.autECLPS.SendKeys [Year], 9, 20
autECLSession.autECLPS.SendKeys [Week], 10, 22
autECLSession.autECLPS.Wait 100
autECLSession.autECLPS.SendKeys "[PF13]"
autECLSession.autECLPS.Wait 100
stämplingkolla = "A"
For ls_rad = 12 To 20
stämpling = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
If stämpling <> stämplingkolla Then
Else
anst = ws.Range("A" & i)
ws2.Cells(xl_rad, 1).Value = anst
arbomrBBLU = autECLSession.autECLPS.GetText(ls_rad, 6, 3)
ws2.Cells(xl_rad, 2).Value = arbomrBBLU
tidstyp = autECLSession.autECLPS.GetText(ls_rad, 11, 1)
ws2.Cells(xl_rad, 3).Value = tidstyp
'Year
ws2.Cells(xl_rad, 4) = ws3.Range("N2").Value
'Week
ws2.Cells(xl_rad, 5) = ws3.Range("O2").Value
tid = autECLSession.autECLPS.GetText(ls_rad, 52, 6)
tid = Fix(tid) + ((tid - Fix(tid)) * 60) / 100
ws2.Cells(xl_rad, 7).Value2 = tid
prest = autECLSession.autECLPS.GetText(ls_rad, 73, 4)
ws2.Cells(xl_rad, 8).Value2 = prest
netto = autECLSession.autECLPS.GetText(ls_rad_netto, 52, 6)
netto = Fix(netto) + ((netto - Fix(netto)) * 60) / 100
ws2.Cells(xl_rad, 9).Value2 = netto
xl_rad = xl_rad + 1
End If
Next ls_rad
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|