|
Hallo zusammen,
ich schreibe seit heute morgen an einem Makro um aus einer Excel Datei Werte in ein anderes Sheets zu übertragen.
Aktuell bringt er mir einen Typenfehler aus und ich verstehe nicht wieso ._. ...
Ist bestimmt nicht der einzige Fehler ^^.
Gibt es eine Möglichkeit den doppelten Array hochzuzählen?, so damit ich nicht 12x das gleiche schreiben muss.
Wen ich zum Beispiel Cache(1(1)) schreib markt er mit das Rot an.
Vielen Dank und Grüße!
Raksha
:Option Explicit
Const OLAP = "U:\Vertrieb\4. Vertriebsinnendienst\1. Auswertungen + Statistiken\OLAP-Auswertungen\Auftragseingang-OLAP.xlsx"
Const UMSATZ = "U:\Vertrieb\2. Anfragen\07.2_01_00_09_12_Anfrageliste_2016.xlsx"
Dim wbkUmsatz As Workbook
Dim shtUmsatz As Worksheet
Sub Ablauf()
Call OLAPDatei
End Sub
Sub OLAPDatei()
Dim geoeffnet As Boolean
Dim monate("Platzhalter", "Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember") As String
Dim sJanuar As String
Dim sFebruar As String
Dim sMaerz As String
Dim sApril As String
Dim sMai As String
Dim sJuni As String
Dim sJuli As String
Dim sAugust As String
Dim sSeptember As String
Dim sOktober As String
Dim sNovember As String
Dim sDezember As String
Dim cache(cJanuar, cFebruar, cMaerz, cApril, cMai, cJuni, cJuli, cAugust, cSeptember, cOktober, cNovember, cSeptember) As Double
Dim cJanuar() As Double
Dim cFebruar() As Double
Dim cMaerz() As Double
Dim cApril() As Double
Dim cMai() As Double
Dim cJuni() As Double
Dim cJuli() As Double
Dim cAugust() As Double
Dim cSeptember() As Double
Dim cOktober() As Double
Dim cNovember() As Double
Dim cSeptember() As Double
Dim k As Double
k = 1
sJanuar = Split(shtUmsatz.Cells(1).Address, "$")(1)
sFebruar = Split(shtUmsatz.Cells(2).Address, "$")(1)
sMaerz = Split(shtUmsatz.Cells(3).Address, "$")(1)
sApril = Split(shtUmsatz.Cells(4).Address, "$")(1)
sMai = Split(shtUmsatz.Cells(5).Address, "$")(1)
sJuni = Split(shtUmsatz.Cells(6).Address, "$")(1)
sJuli = Split(shtUmsatz.Cells(7).Address, "$")(1)
sAugust = Split(shtUmsatz.Cells(8).Address, "$")(1)
sSeptember = Split(shtUmsatz.Cells(9).Address, "$")(1)
sOktober = Split(shtUmsatz.Cells(10).Address, "$")(1)
sNovember = Split(shtUmsatz.Cells(11).Address, "$")(1)
sDezember = Split(shtUmsatz.Cells(12).Address, "$")(1)
If IsWorkbookOpen(OLAP) Then
geoeffnet = True
Else
OpenWorkbook (OLAP)
geoeffnet = True
End If
If geoeffnet = True Then
For i = 1 To 12
cache(0 + k) = ActiveSheet.Cells(13, 2).Value 'Zeilenbeschriftung V
cache(0 + k) = ActiveSheet.Cells(12, 2).Value 'Zeilenbeschriftung T
cache(0 + k) = ActiveSheet.Cells(9, 2).Value 'Zeilenbeschriftung L
k = k + 1
Loop
Workbooks("07.2_01_00_09_12_Anfrageliste_2016.xlsx").Activate
Worksheets(2).Range("G57").Value = cache(1)
Worksheets(2).Range("G58").Value = cache(1)
Worksheets(2).Range("G59").Value = cache(1)
Worksheets(4).Range("G57").Value = cache(2)
Worksheets(4).Range("G58").Value = cache(2)
Worksheets(4).Range("G59").Value = cache(2)
Worksheets(6).Range("G57").Value = cache(3)
Worksheets(6).Range("G58").Value = cache(3)
Worksheets(6).Range("G59").Value = cache(3)
Worksheets(8).Range("G57").Value = cache(4)
Worksheets(8).Range("G58").Value = cache(4)
Worksheets(8).Range("G59").Value = cache(4)
Worksheets(10).Range("G57").Value = cache(5)
Worksheets(10).Range("G58").Value = cache(5)
Worksheets(10).Range("G59").Value = cache(5)
Worksheets(12).Range("G57").Value = cache(6)
Worksheets(12).Range("G58").Value = cache(6)
Worksheets(12).Range("G59").Value = cache(6)
Worksheets(14).Range("G57").Value = cache(7)
Worksheets(14).Range("G58").Value = cache(7)
Worksheets(14).Range("G59").Value = cache(7)
Worksheets(16).Range("G57").Value = cache(8)
Worksheets(16).Range("G58").Value = cache(8)
Worksheets(16).Range("G59").Value = cache(8)
Worksheets(18).Range("G57").Value = cache(9)
Worksheets(18).Range("G58").Value = cache(9)
Worksheets(18).Range("G59").Value = cache(9)
Worksheets(20).Range("G57").Value = cache(10)
Worksheets(20).Range("G58").Value = cache(10)
Worksheets(20).Range("G59").Value = cache(10)
Worksheets(22).Range("G57").Value = cache(11)
Worksheets(22).Range("G58").Value = cache(11)
Worksheets(22).Range("G59").Value = cache(11)
Worksheets(24).Range("G57").Value = cache(12)
Worksheets(24).Range("G58").Value = cache(12)
Worksheets(24).Range("G59").Value = cache(12)
ActiveWorkbook.SaveAs UMSATZ
ActiveWorkbook.Close
End Sub
|