Hi all!
I'm having a bit of a struggle here . I've managed to make decent progress with my VB skills, for example I've recreated a script which replaces 50.000 INDIRECT() function occurences and speeds up the Workbook by a factor of zillion. I am however, having problem with the last step -
> loop through 2-dim Array1 (concatenated strings and row numbers)
> search for these strings in the 2-dim Array2 (strings and amounts)
> if found, write amount from Array2 into row from Array1
It fails in the last block, just after this comment: "---------perform search and write into destination". I know because I've been working on the code little bit by little bit and testing with MsgBox-es, writing into empty sheet etc.
Can anyone spot what is wrong with the function call/for loop and why nothing gets written in the destination?
Many thanks!
I'm having a bit of a struggle here . I've managed to make decent progress with my VB skills, for example I've recreated a script which replaces 50.000 INDIRECT() function occurences and speeds up the Workbook by a factor of zillion. I am however, having problem with the last step -
> loop through 2-dim Array1 (concatenated strings and row numbers)
> search for these strings in the 2-dim Array2 (strings and amounts)
> if found, write amount from Array2 into row from Array1
It fails in the last block, just after this comment: "---------perform search and write into destination". I know because I've been working on the code little bit by little bit and testing with MsgBox-es, writing into empty sheet etc.
Can anyone spot what is wrong with the function call/for loop and why nothing gets written in the destination?
Many thanks!
Code:
'====================================================================
'This proceedure retrieves 'Amount Spent' per campaign form sheets 'labeled 'jan', 'feb', 'mar', ...
' and writes them onto 'rawData' Sheet.
'From there, data gets updated in the pivot tables on sheets 'search', 'gdn', 'youtube'.
'STILL UNDER CONSTRUCTION
'====================================================================
Public Month As String
Private SourceSheet As Worksheet
Private CampaignsCount As Integer
Private arrCampaignsAmounts() As Variant
Private StringsCount As Long
Private arrStrings() As Variant
Private strBaba As String
Public Function IsInArray(ByVal stringToBeFound As String, arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function
Public Sub stringComparison()
Call OptimizeCode_Begin
'------------------- get user input, which month (sheet) are we working on
ChooseMonthUserform.Show
Set SourceSheet = Worksheets(Month)
With SourceSheet
CampaignsCount = Application.WorksheetFunction.CountA(.Range("F:F")) - 1
ReDim arrCampaignsAmounts(1 To CampaignsCount, 1 To 2)
'------------------- read Campaigns/Amounts from .csv, write into an array
For i = 1 To CampaignsCount
k = 6
For j = 1 To 2
arrCampaignsAmounts(i, j) = .Cells(i + 11, k).Value
k = 9
Next j
Next i
End With
'-------------------- load strings to search for into another array
Set SourceSheet = Sheet2
With SourceSheet
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
StringsCount = (lastrow - 1) / 12
ReDim arrStrings(1 To StringsCount, 1 To 2)
k = 1
For i = 1 To StringsCount
arrStrings(i, 1) = .Range("A" & i + k).Value & "_" & .Range("C" & i + k).Value & "_" & .Range("D" & i + k).Value & "_" & .Range("E" & i + k).Value
arrStrings(i, 2) = .Range("A" & i + k).Row
k = k + 11
Next i
'Sheet11.Range("A1").Resize(UBound(arrStrings, 1), UBound(arrStrings, 2)) = _
'arrStrings
'[B][COLOR=#008000]-------------------- perform search and write into destination[/COLOR][/B]
For i = 1 To UBound(arrStrings)
If IsInArray(arrStrings(i, 1), arrCampaignsAmounts) = True Then
SourceSheet.Range("H" & arrStrings(i, 2)) = arrCampaignsAmounts(i, 2)
Else
End If
Next i
End With
'===============================================================================
'TO DO:
'> redesign the second array so that it takes into account which month it is, which row to start from
' (jan - row #2, feb - row#3, mar - row #4, ...)
'> try and read into arrays all at once without loops
'===============================================================================
Call OptimizeCode_End
End Sub