14. december 2015 - 13:51
Der er
2 kommentarer og 1 løsning
VBA loop
Hej Eksperter Jeg har brug for hjælp til at skrive et loop. Jeg har 120 filer hvor skal tilføje 67 forskellige formler I 33 forskellige kolonner. Jeg har i forvejen et loop jeg kan bruge til at gennemløbe de 120 filer, men jeg har brug for hjælp til at skrive loopet der skal indsætte de 67 forskellige former I de 33 kolonner. Jeg har koden der skriver de 67 formler ind I kolonne E på de rigtige rækker: Sub Formler() Range("E10").FormulaR1C1 = "=R[2]C+R[368]C+R[408]C" Range("E12").FormulaR1C1 = "=R[1]C+R[18]C+R[23]C+R[28]C+R[33]C+R[281]C+R[324]C+R[346]C" Range("E13").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[11]C+R[14]C)" Range("E14").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E21").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range("E24").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range("E27").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range("E30").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range("E35").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range("E40").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range("E45").FormulaR1C1 = "=R[1]C+R[58]C+R[91]C+R[140]C+R[165]C+R[174]C+R[207]C" Range("E46").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C+R[41]C+R[49]C" Range("E47").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E55").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E63").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E71").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E79").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E87").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E95").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E103").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C" Range("E104").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E112").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E120").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E128").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E136").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C+R[41]C" Range("E137").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E145").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E153").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E161").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E169").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E177").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E185").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C" Range("E186").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E194").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E202").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E210").FormulaR1C1 = "=R[1]C" Range("E211").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E219").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C" Range("E220").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E228").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E236").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E244").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E252").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C" Range("E253").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E261").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E269").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E277").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E285").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range("E293").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[15]C+R[22]C+R[29]C+R[36]C)" Range("E294").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E301").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E308").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E315").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E322").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E329").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E336").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[15]C)" Range("E337").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E344").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E351").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range("E358").FormulaR1C1 = "=SUM(R[1]C+R[6]C+R[10]C)" Range("E359").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range("E364").FormulaR1C1 = "=SUM(R[1]C:R[3]C)" Range("E368").FormulaR1C1 = "=SUM(R[1]C:R[8]C)" Range("E378").FormulaR1C1 = "=SUM(R[1]C:R[38]C)" Range("E418").FormulaR1C1 = "=SUM(R[1]C:R[5]C)" Range("E426").FormulaR1C1 = "=SUM(R[1]C:R[38]C)" End Sub Ovenstående formler skal indsættes I de følgende kolonner: C E:P S:AD AG:AJ AM AO AQ AS Nogen der kan hjælpe med at skrive mit loop - meget gerne så det er nemt at ændre kolonnerne som formlerne skal indsættes i? Mvh Line
Annonceindlæg fra Infor
14. december 2015 - 18:23
#1
Sub Formler() Dim Kol As Variant, I As Integer, C As String Kol = Split("C E F G H I J K L M N O P S T U V W X Y Z AA AB AC AD AG AH AI AJ AM AO AQ AS", " ") For I = 0 To UBound(Kol) C = Kol(I) Range(C & "10").FormulaR1C1 = "=R[2]C+R[368]C+R[408]C" Range(C & "12").FormulaR1C1 = "=R[1]C+R[18]C+R[23]C+R[28]C+R[33]C+R[281]C+R[324]C+R[346]C" Range(C & "13").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[11]C+R[14]C)" Range(C & "14").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "21").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range(C & "24").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range(C & "27").FormulaR1C1 = "=SUM(R[1]C:R[2]C)" Range(C & "30").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range(C & "35").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range(C & "40").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range(C & "45").FormulaR1C1 = "=R[1]C+R[58]C+R[91]C+R[140]C+R[165]C+R[174]C+R[207]C" Range(C & "46").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C+R[41]C+R[49]C" Range(C & "47").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "55").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "63").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "71").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "79").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "87").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "95").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "103").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C" Range(C & "104").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "112").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "120").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "128").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "136").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C+R[41]C" Range(C & "137").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "145").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "153").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "161").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "169").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "177").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "185").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C" Range(C & "186").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "194").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "202").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "210").FormulaR1C1 = "=R[1]C" Range(C & "211").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "219").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C" Range(C & "220").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "228").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "236").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "244").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "252").FormulaR1C1 = "=R[1]C+R[9]C+R[17]C+R[25]C+R[33]C" Range(C & "253").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "261").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "269").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "277").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "285").FormulaR1C1 = "=SUM(R[1]C:R[7]C)" Range(C & "293").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[15]C+R[22]C+R[29]C+R[36]C)" Range(C & "294").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "301").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "308").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "315").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "322").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "329").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "336").FormulaR1C1 = "=SUM(R[1]C+R[8]C+R[15]C)" Range(C & "337").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "344").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "351").FormulaR1C1 = "=SUM(R[1]C:R[6]C)" Range(C & "358").FormulaR1C1 = "=SUM(R[1]C+R[6]C+R[10]C)" Range(C & "359").FormulaR1C1 = "=SUM(R[1]C:R[4]C)" Range(C & "364").FormulaR1C1 = "=SUM(R[1]C:R[3]C)" Range(C & "368").FormulaR1C1 = "=SUM(R[1]C:R[8]C)" Range(C & "378").FormulaR1C1 = "=SUM(R[1]C:R[38]C)" Range(C & "418").FormulaR1C1 = "=SUM(R[1]C:R[5]C)" Range(C & "426").FormulaR1C1 = "=SUM(R[1]C:R[38]C)" Next End Sub
04. januar 2016 - 09:17
#3
Det virker præcis som ønsket - tusinde tak for hjælpen. ...og beklager den lange svartid, havde problemer med at få eksperten.dk til at fungere før jul, og så glemte jeg dig igen. Mvh Line