Hi there!
Beforehand, if you need any extra information, as I may have missed something you need then feel free to ask me any questions. Many thanks!
I am in dire need of your help to speed up two macro's. The names in the macro are Dutch, though that shouldn't matter. The macro was written by someone else in the past, but is way too slow now.
And I am not that expertised in VBA code.
Basically I have a sheet called 'Input Kostprijsanalyse' this is where a lot of data validation cells are that extract the data from the "Input SQL" Sheet. So if I pick "Calcnr" in the data validation cell everything else get's
filled in at the "Input Kostprijsanalyse" sheet.
Basically how the Macro works now is that it converts the input from "Input Kostprijsanalyse" and converts it to the logfile with corresponding values(See below macros).
It then deletes the second row of "Input SQL" and then clears the content in O14 of "Input Kostprijsanalyse"(Calcnr). Then loops to again the second row of "Input SQL" etc etc, until there are no more lines left.
As you may see in the bulk macro is that there is use of Activate and Select statements which to my opinion drastically slow down the process.
Below is the first macro
Second Macro that collaborates with the first macro ("Blad19") is the "Input SQL" Sheet. "Blad 1 is the "Input Kostprijsanalyse" Sheet. "Blad 7" is the Logfile Sheet.
Cell O14 is the calcnr cell
Beforehand, if you need any extra information, as I may have missed something you need then feel free to ask me any questions. Many thanks!
I am in dire need of your help to speed up two macro's. The names in the macro are Dutch, though that shouldn't matter. The macro was written by someone else in the past, but is way too slow now.
And I am not that expertised in VBA code.
Basically I have a sheet called 'Input Kostprijsanalyse' this is where a lot of data validation cells are that extract the data from the "Input SQL" Sheet. So if I pick "Calcnr" in the data validation cell everything else get's
filled in at the "Input Kostprijsanalyse" sheet.
Basically how the Macro works now is that it converts the input from "Input Kostprijsanalyse" and converts it to the logfile with corresponding values(See below macros).
It then deletes the second row of "Input SQL" and then clears the content in O14 of "Input Kostprijsanalyse"(Calcnr). Then loops to again the second row of "Input SQL" etc etc, until there are no more lines left.
As you may see in the bulk macro is that there is use of Activate and Select statements which to my opinion drastically slow down the process.
Below is the first macro
VBA Code:
Sub Log()
'LastRow = Sheets("logfile").Cells(Rows.Count, "C").End(xlUp).Row
Lastrow = Sheets("logfile").Cells.Find(What:="*", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Lastrow = Application.WorksheetFunction.Max(Lastrow + 1, 10)
Debug.Print Lastrow
'read
Datum = Range("datum").value
klant_naam = Range("klant_naam").value
Klantnummer = Range("Klantnummer").value
Bedrijf = Range("Bedrijf").value
Klantrating = Range("Klantrating").value
calcnr = Range("calcnr").value
aanmaakdatum = Range("aanmaakdatum").value
contract_nummer = Range("Contract_nummer").value
kenteken = Range("kenteken").value
Datum_ingang = Range("Datum_ingang").value
Calculatie_mantel = Range("Calculatie_mantel").value
Type_lease = Range("Type_lease").value
Sale_LB = Range("Sale_LB").value
Speciale_afspraak = Range("Speciale_afspraak").value
merk_input = Range("merk_input").value
model_input = Range("model_input").value
geelgrijs_input = Range("geelgrijs_input").value
brandstof_input = Range("brandstof_input").value
investering = Range("investering").value
restwaarde = Range("restwaarde").value
restwaarde_perc = Range("restwaarde_perc").value
basis_restwaarde = Range("basis_restwaarde").value
basis_restwaardepercentage = Range("basis_restwaardepercentage").value
geschatte_verkoopwaarde = Range("geschatte_verkoopwaarde").value
looptijd = Range("looptijd_input").value
kilometers = Range("totaal_kilometers").value
rente = Range("rente_input").value
kostprijs_rente = Range("kostprijs_rente").value
commercieel = Range("commercieel").value
Aanpassing_commercieel = Range("Aanpassing_commercieel").value
overhead = Range("overhead").value
speciale_afspraak_bedrag = Range("speciale_afspraak_bedrag").value
opbrengst_totaal = Range("opbrengsten_totaal").value
opbrengst_maand = Range("opbrengsten_maand").value
kosten_totaal = Range("kosten_totaal").value
kosten_maand = Range("kosten_maand").value
marge_totaal = Range("marge_totaal").value
marge_maand = Range("marge_maand").value
margeperc = Range("margeperc").value
verkoopresultaat = Range("verkoopresultaat").value
verkoopresultaatnto = Range("verkoopresultaatnto").value
verkoop_perc = Range("verkoop_perc").value
margebruto = Range("margebruto").value
marge_bto_perc = Range("marge_bto_perc").value
overhead_kosten = Range("overhead_kosten").value
marge_netto = Range("marge_netto").value
marge_nto_perc = Range("marge_nto_perc").value
marge_basis = Range("marge_basis").value
marge_basis_perc = Range("marge_basis_perc").value
afschropbr = Range("afschropbr").value
verkoopresultaatnto = Range("verkoopresultaatnto").value
verzopbr = Range("verzopbr").value
verzmarge = Range("verzmarge").value
hsbopbr = Range("hsbopbr").value
hsbmarge = Range("hsbmarge").value
roopbr = Range("roopbr").value
romarge = Range("romarge").value
bopbr = Range("bopbr").value
bmarge = Range("bmarge").value
vvopbr = Range("vvopbr").value
vvmarge = Range("vvmarge").value
overheadopbr = Range("overheadopbr").value
overheadmarge = Range("overheadmarge").value
brandstofopbr = Range("brandstofopbr").value
brandstofmarge = Range("brandstofmarge").value
renteopbr = Range("renteopbr").value
rentemarge = Range("rentemarge").value
commopbr = Range("commopbr").value
commmarge = Range("commmarge").value
contract_status = Range("contract_status").value
invuller = Range("invuller").value
'log
Sheets("logfile").Select
Cells(Lastrow, 3).value = Datum
Cells(Lastrow, 4).value = klant_naam
Cells(Lastrow, 5).value = Klantnummer
Cells(Lastrow, 6).value = Bedrijf
Cells(Lastrow, 7).value = Klantrating
Cells(Lastrow, 8).value = calcnr
Cells(Lastrow, 9).value = aanmaakdatum
Cells(Lastrow, 10).value = contract_nummer
Cells(Lastrow, 11).value = kenteken
Cells(Lastrow, 12).value = Datum_ingang
Cells(Lastrow, 13).value = Calculatie_mantel
Cells(Lastrow, 14).value = Type_lease
Cells(Lastrow, 15).value = Sale_LB
Cells(Lastrow, 16).value = Speciale_afspraak
Cells(Lastrow, 17).value = merk_input
Cells(Lastrow, 18).value = model_input
Cells(Lastrow, 19).value = geelgrijs_input
Cells(Lastrow, 20).value = brandstof_input
Cells(Lastrow, 21).value = investering
Cells(Lastrow, 22).value = restwaarde
Cells(Lastrow, 23).value = restwaarde_perc
Cells(Lastrow, 24).value = basis_restwaarde
Cells(Lastrow, 25).value = basis_restwaardepercentage
Cells(Lastrow, 26).value = geschatte_verkoopwaarde
Cells(Lastrow, 27).value = looptijd
Cells(Lastrow, 28).value = kilometers
Cells(Lastrow, 29).value = rente
Cells(Lastrow, 30).value = kostprijs_rente
Cells(Lastrow, 31).value = commercieel
Cells(Lastrow, 32).value = Aanpassing_commercieel
Cells(Lastrow, 33).value = overhead
Cells(Lastrow, 34).value = speciale_afspraak_bedrag
Cells(Lastrow, 35).value = opbrengst_totaal
Cells(Lastrow, 36).value = opbrengst_maand
Cells(Lastrow, 37).value = kosten_totaal
Cells(Lastrow, 38).value = kosten_maand
Cells(Lastrow, 39).value = marge_totaal
Cells(Lastrow, 40).value = marge_maand
Cells(Lastrow, 41).value = margeperc
Cells(Lastrow, 42).value = verkoopresultaat
Cells(Lastrow, 43).value = verkoopresultaatnto
Cells(Lastrow, 44).value = verkoop_perc
Cells(Lastrow, 45).value = margebruto
Cells(Lastrow, 46).value = marge_bto_perc
Cells(Lastrow, 47).value = overhead_kosten
Cells(Lastrow, 48).value = marge_netto
Cells(Lastrow, 49).value = marge_nto_perc
Cells(Lastrow, 50).value = marge_basis
Cells(Lastrow, 51).value = marge_basis_perc
Cells(Lastrow, 52).value = afschropbr
Cells(Lastrow, 53).value = verkoopresultaatnto
Cells(Lastrow, 54).value = verzopbr
Cells(Lastrow, 55).value = verzmarge
Cells(Lastrow, 56).value = hsbopbr
Cells(Lastrow, 57).value = hsbmarge
Cells(Lastrow, 58).value = roopbr
Cells(Lastrow, 59).value = romarge
Cells(Lastrow, 60).value = bopbr
Cells(Lastrow, 61).value = bmarge
Cells(Lastrow, 62).value = vvopbr
Cells(Lastrow, 63).value = vvmarge
Cells(Lastrow, 64).value = overheadopbr
Cells(Lastrow, 65).value = overheadmarge
Cells(Lastrow, 66).value = brandstofopbr
Cells(Lastrow, 67).value = brandstofmarge
Cells(Lastrow, 68).value = renteopbr
Cells(Lastrow, 69).value = rentemarge
Cells(Lastrow, 70).value = commopbr
Cells(Lastrow, 71).value = commmarge
Cells(Lastrow, 72).value = contract_status
Cells(Lastrow, 73).value = invuller
End SubSecond Macro that collaborates with the first macro ("Blad19") is the "Input SQL" Sheet. "Blad 1 is the "Input Kostprijsanalyse" Sheet. "Blad 7" is the Logfile Sheet.
Cell O14 is the calcnr cell
VBA Code:
Sub Bulk()
'screen updating
Application.ScreenUpdating = False
'declare
Dim calcnr As String
Dim i As Integer
Dim iCount As Integer
Dim DoubleCount As Integer
Dim TotalCount As Integer
'determine
i = 1
iCount = Application.WorksheetFunction.CountA(Blad19.Range("A:A")) - 1
'loop
For i = 1 To iCount
'read
calcnr = Range("calcnr").value
If calcnr = "" Then
calcnr = Blad19.Range("A2")
Range("calcnr") = calcnr
End If
'check
If Not Blad7.Range("H:H").Find(calcnr) Is Nothing Then
DoubleCount = DoubleCount + 1
Else
'execute
Module2.Log
TotalCount = TotalCount + 1
End If
'erase
Blad19.Activate
Rows(2).Select
Selection.Delete
Blad1.Activate
Range("O14").Select
Selection.ClearContents
Next i
'screen updating
Application.ScreenUpdating = True
'erase
frmWait.lblWait.Caption = ""
'close
frmWait.Hide
'message
MsgBox TotalCount & " calculatie(s) gelogd" & vbCrLf & DoubleCount & " dubbele calculatie(s) niet toegevoegd"
End Sub
?){kind=link}