Missing last step - search string from one array in another, write back to Worksheet

[astrbac]

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!

'====================================================================
'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

 

[skywriter]

You say it fails, but that's not much of an explanation for someone trying to help you.

Here's your code.

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

I would suggest you put a break point at the first line of this section of code and step through it.

1) What does your function IsInArray evaluate to?

2) If true, then you should look in the locals window and see what the value of i is and then look into your arrayStrings and see what the value of element (i,2) is, it should be a number, right? This will tell you what cell it should be using and finally what is the value of arrCampaignsAmount(i,2), since this is what will end up in the cell defined by - SourceSheet.Range("H" & arrStrings(i, 2)).

 

[astrbac]

Dear Skywriter,

thank you for your help, much appreciated!

When you say "set breakpoint" do you mean "dark red line" (press F9)? I know how to do this but am unsure of how to step through the code. I normally debug with F8 and go line by line which can get quite tiresome, especially if you have huge loops...

The function IsInArray should evaluate to True/False; if it evaluates to True, values from arrCampaignsAmounts should be written to the Sheet. It's difficult to explain with words like this (I'm an economics guy turned self taught programmer so please excuse my poor abilities).

If the function finds a string from arrStrings in arrCampaignsAmounts => value from 2nd dimension of arrCampaignsAmounts, at the index where the string was located, should be written to the Sheet - Column H, row number same as from 2nd dimension of arrStrings where string was located.

If not => move on to the next i (arrStrings index, i.e. string)


Ahh ... seems impossible to explain

 

[skywriter]

When you say "set breakpoint" do you mean "dark red line" (press F9)? I know how to do this but am unsure of how to step through the code.

Yes a break point is the dark red line. A break point allows you to press F5 instead of F8 at the beginning of your code and your code will run to the break point, then you can use F8 to step through it a line at a time from the break point.

The function IsInArray should evaluate to True/False

I understand what your code does by reading it. I'm trying to help you trouble shoot it. I asked you to do something and It appears rather than do that, you feel the solution is to explain your code to me.

I suggest if you come on this board and someone who is trying to help you asks you to do something and report the results, just do it and report the results.

 

[astrbac]

Bruce, I apologize, I did not mean to come across as someone who wants to have it on a silver platter .

I tried stepping through that piece of code as you instructed me (thanks for teaching me how to use debugger properly):

> flow enters the For loop properly
> function call works and "jumps" to the function definition, up there (see screenshot)
> string to be searched for gets passed properly; array within to search I BELIEVE gets passed too (I can't confirm this)
> function always returns FALSE (?)

It's got to be something related to function definition itself. Unfortunately, I didn't write it, I know what it does (basically, it's a MATCH() function that you write into cells and I use this frequently).

I did have a problem with "Type mismatch", that's why I Googled and found a solution which instructed to insert "ByVal" part into the function. I don't understand what this does but the error went away.

Any tips?

Thanks for taking the time to help me out

 

[skywriter]

No worries, I just don't want to go back and forth too many times.

Any possibility you can upload a desensitized workbook to a site I can download from?

Dropbox or google?

Don't sweat this too much, work with me and I'll get you to where you need to be.

Right now it's very late here, a website I visited tells me it's almost 10 in the morning in Zagreb, it's almost 1 in the morning where I am, so you are 9 hours ahead of me.

I'll check on this in about 8 or so hours.

 

[astrbac]

No problem, 8 hours I can wait (and more) . I've sent you a file via private message here on the forum.

Cheers!

 

[skywriter]

I don't know why I didn't remember that you can't use match on multi dimension arrays.

I used looping, exiting the loop and moving onto the next string when it finds a match.

'-------------------- perform search and write into destination

Dim jj As Long
            For i = 1 To UBound(arrStrings)
                For jj = LBound(arrCampaignsAmounts, 1) To UBound(arrCampaignsAmounts, 1)
                If arrStrings(i, 1) = arrCampaignsAmounts(jj, 1) Then
            
'                If IsInArray(arrStrings(i, 1), arrCampaignsAmounts) = True Then
                    SourceSheet.Range("H" & arrStrings(i, 2)) = arrCampaignsAmounts(jj, 2)
                    GoTo 1
                End If
                Next jj
1            Next i
        End With

 

[astrbac]

Since we are all (more or less) learning AND working or using this knowledge for work so we have to respect deadlines, we rarely take the time to properly say thanks and give feedback.

I wish to use this opportunity to say THANK YOU Bruce, for taking the time end effort to go through the gibberish that is my noob-code and also to teach me a new thing or two.

I can quite proudly say that i have improved my "application" by a factor of 1000, created a few new modules, learned how to optimize for speed and accuracy, call procedures from other modules and basically - I have automated a VERY "painful" manual workflow with just this one workbook.

There's a lot of room to grow but this was a major breakthrough.

Cheers!

P.S. If anyone needs this workbook I'd be more than glad to share but I doubt that anyone has the same case :D

 

[skywriter]

My pleasure, thanks for the feedback.