VBA Integer Adding with Offset Cells

[turbo805]

Hey everyone!

I'm working on a macro that finds a category in my spreadsheet and adds the offset values to integers and then pastes these integers in a different sheet/cells. I'm not getting an error but my values are returning as 0 when I should be getting numbers.

Here's my code:

Sub TEST()'
' TEST Macro

Dim OrganicClicks As Integer
Dim OrganicLeads As Integer
Dim OrganicCons As Integer

Sheets("PivotTable").SelectRange("A1").Select
On Error Resume Next
Cells.Find(What:="cq2o", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    If ActiveCell.Value = "cq2o" Then
        OrganicClicks = (OrganicClicks + ActiveCell.Offset(0, 1))
        OrganicLeads = (OrganicLeads + ActiveCell.Offset(0, 2))
        OrganicCons = (OrganicCons + ActiveCell.Offset(0, 3))
    Else
    End If
    
Sheets("Misc.").Select
Range("D185").Select
Do Until ActiveCell = ""
        ActiveCell.Offset(0, 1).Select
        Loop
        Range("ActiveCell").Value = OrganicClicks
        Range("ActiveCell.Offset(1,0)").Value = OrganicLeads
        Range("ActiveCell.Offset(2,0)").Value = OrganicCons
        
End Sub

Thanks in advance.

 

[My Aswer Is This]

I would like to know what your wanting to do.
I always like knowing what the ultimate goal is and then write a script to do what you want.
Trying to understand what a script is doing and then fix it is not something I like doing.

I feel like your script is doing a lot of steps that are not needed.

 

[turbo805]

Sure.

So my script is looking for categories in a daily updated PivotTable--in this case it's the category "cq2o"--and then taking the corresponding values with that category and pasting them in another sheet.
I have a broad miscellaneous sheet that i'm adding these values to. So essentially, i'm trying to take multiple low value categories (i'll add other categories like "cg2o" to my Organic integers before pasting them), add them all together, and then paste the final results in my Misc. sheet.

Here's an image of my PivotTable to kind of get a perspective: https://paste.pics/736a2e7214ab0f103596ab4d5cf5f9b1
Also, as a background, the reason I have to search for "cg2o" first is because my PivotTable categories change slightly daily and on some rare days the category "cg2o" might not be there.

I'm fairly new to vba so I understand if my code thought process is a little jumbled!

 

[My Aswer Is This]

Thanks. I'm only slightly familiar with PivotTables.
So I may be of no help.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Akuini]

I don't understand this part:

Sheets("Misc.").Select
Range("D185").Select
Do Until ActiveCell = ""
        ActiveCell.Offset(0, 1).Select
        Loop

Do you mean you want to select the first blank cell to the right (not below?) of cell D185? Because 'Offset(0, 1)' means 1 column to the right.

 

[turbo805]

Yes, exactly.

Here's how my Misc. sheet is set up to give some background on this: https://paste.pics/3d264c69f62d43779becd26f7ef272cb

I'm going to be pressing my Macro once everyday to automatically update the numbers for me. Looking for the first blank cell to the right ensures that it goes to the next day everytime I press it.

My "Misc." sheets clicks, leads, and conversions, are all going to be cumulative numbers from different categories in my PivotTable on another sheet, which is why i'm trying to add them all together through integers before I paste them in Misc.

 

[Akuini]

Yes, exactly.

Here's how my Misc. sheet is set up to give some background on this: https://paste.pics/3d264c69f62d43779becd26f7ef272cb

I'm going to be pressing my Macro once everyday to automatically update the numbers for me. Looking for the first blank cell to the right ensures that it goes to the next day everytime I press it.

My "Misc." sheets clicks, leads, and conversions, are all going to be cumulative numbers from different categories in my PivotTable on another sheet, which is why i'm trying to add them all together through integers before I paste them in Misc.

Ok, try this:
This line:

rc = Rows(185).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

means that the code will find the last column with data in row 185 then offset 1 column to right (i.e the next empty cell).

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1077526a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077526-vba-integer-adding-offset-cells.html#post5176697[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] rc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] r [B][COLOR=Royalblue]As[/COLOR][/B] Range
[B][COLOR=Royalblue]Dim[/COLOR][/B] txt [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] wsM [B][COLOR=Royalblue]As[/COLOR][/B] Worksheet

[B][COLOR=Royalblue]Set[/COLOR][/B] wsM = Sheets([COLOR=brown]"Misc."[/COLOR])
[B][COLOR=Royalblue]With[/COLOR][/B] Sheets([COLOR=brown]"PivotTable"[/COLOR])
    [B][COLOR=Royalblue]Set[/COLOR][/B] r = .Range([COLOR=brown]"A1"[/COLOR], .Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp))
    txt = [COLOR=brown]"cq2o"[/COLOR]
    [B][COLOR=Royalblue]If[/COLOR][/B] [B][COLOR=Royalblue]Not[/COLOR][/B] IsError(Application.Match(txt, r, [COLOR=crimson]0[/COLOR])) [B][COLOR=Royalblue]Then[/COLOR][/B]
        n = Application.Match(txt, r, [COLOR=crimson]0[/COLOR])
        rc = Rows([COLOR=crimson]185[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + [COLOR=crimson]1[/COLOR]
        wsM.Range(wsM.Cells([COLOR=crimson]185[/COLOR], rc), wsM.Cells([COLOR=crimson]187[/COLOR], rc)) = Application.Transpose(.Range(.Cells(n, [COLOR=brown]"B"[/COLOR]), .Cells(n, [COLOR=brown]"D"[/COLOR])))
    [B][COLOR=Royalblue]Else[/COLOR][/B]
        MsgBox [COLOR=brown]"Can't find "[/COLOR] & txt
    [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

 

[turbo805]

Thanks.

This worked well, simplified my code, and taught me somethings in the process.

Much appreciated.

 

[Akuini]

Thanks.

This worked well, simplified my code, and taught me somethings in the process.

Much appreciated.

Your welcome & thanks for replying