Setup and Write to a temporary storage location.

[YellowLabPro]

This is the next part I need to solve for, I will have several more. I am approaching this differently than before. This time I looked at the entire project, listed out what needed to be accomplished and then approaching them as independent tasks. I even wrote out a list....

I have never done a couple of things that I need to do here. A short explanation first.

I am searching for records that meet certain criteria. If the record meets the criteria, then I need to look at its qty and store it in a container, find the next record that meets the criteria and look at its qty and add its qty to the container, do this again until I have found all the like records. Once that is done look at the sum of all of the records found.

What I have never done, and is vague to me, is how to set up a variable or temporary storage location in memory to add the quantities of the records.
So the first loop would return a qty of 5 for the record 325605BW. I will write this value to the destination sheet.
The second loop would return a qty of 6 for the record 3256LASBGR, and I will again write this to the destination sheet.

So in these values:
3256305BW
3256305BW10 qty of 2
3256305BW11 qty of 3
3256LASBGR
3256LASBGR10 qty of 1
3256LASBGR11 qty of 0
3256LASBGR7 qty of 2
3256LASBGR8 qty of 0
3256LASBGR9 qty of 1
3256LASBGR95 qty of 2
3256LASBK

I do not know how to setup and write to a variable in thus manner.


Thanks

[Bob Phillips]

Doug,

Sensible approach.

But why would you write it to a worksheet? It seems better to me to just accumulate it in a VBA variable, and then do with it what you will at the end.

[YellowLabPro]

BTW:
Here is where I am starting from. It may be of some use. I am using the criteria of ~P to be the value to initiate and uniquely identify what records to sort on.


With ActiveSheet
If Left(.Range("C4"), 3) = "~P " Then
If .Range("B4").Offset(1, 0).Value Like .Range("B4").Value & "*" Then
.Range("B4").Offset(1, 0).Activate

End If: End If
End With

[YellowLabPro]

Thanks Bob,
That is a good point. I think I am limiting myself to what I know or can see; each step of the way through solving for this. What I ultimately will want to do is if the summed value is greater than zero then I will write a message to the sheet, i.e. "Get Photo".

[Bob Phillips]

You can see by adding the variable to the watch window and stepping the code.

[YellowLabPro]

I am not there yet, I would need some assistance in going about it in that fashion. I usually set it up by doing something on the sheet, like activating the cell.

But I think more to the point of my lacking is I don't know how to get started to setting up this variable container of which I refer to and then doing something to it, like adding more qty to the existing amount and tallying. This is one of those things that after I see it and work through it several times I then can begin to comprehend how it works.

[Bob Phillips]

That would then be something like


With ActiveSheet
If Left(.Range("C4"), 3) = "~P " Then
If .Range("B4").Offset(1, 0).Value Like .Range("B4").Value & "*" Then
Worksheets(.Range("B4").Offset(1, 0).Value).Range("A1").Value = _
.Range("C4").Offset(1, 0).Value
End If
End If
End With


this is your code without a loop of course

[YellowLabPro]

Sorry Bob,
confused on this part:

Worksheets(.Range("B4").Offset(1, 0).Value).Range("A1").Value = _
.Range("C4").Offset(1, 0).Value


I hope I did not mislead you in thinking I had to write to a sheet, I don't. I mean the temporary storage to be a memory location.
It appears that is what you are having this line of code above do, that is write to the sheet.
But also there is an error for the out of Range. I am guessing you have Worksheets in there for to setup the condition to write to a particular sheet.

What I wanted to do is get started on how to write to a memory location and then do it again until the loop completes.
Yes you are correct, I do not have the loop in there currently. I was going to address that after I nailed down the concepts.

[Bob Phillips]

Dim mySum As Double
Dim LastRow As Long
Dim i As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 5 To LastRow
If Left(.Range("C4"), 3) = "~P " Then
If .Cells(i, "B").Value Like .Range("B4").Value & "*" Then
mySum = mySum + .Cells(i, "C").Value
End If
End If
Next i
End With

[Zack Barresse]

The key question is, will you be stopping code whilst between the routines? If so, you'll need to think of a way to keep the variable in memory, which a standard routine varible will be lost on [lost] focus. Think Static (variable type), SaveSetting() to the registry, writing to a worksheet as Bob mentioned, or a collection.

[Bob Phillips]

No he didn't, Bob said specifically not to write to the worksheet.

[Zack Barresse]

Well Zack must have read too fast then (psst, he does that sometimes). ;)

[Bob Phillips]

I know, he's a real pain. I wouldn't have anything to do with him if I were you!

[Zack Barresse]

I would, but the more I try, the more this stalker keeps following me. I mean he pops up every time I walk by a mirror!

[Bob Phillips]

Don't look you narcissist!

[Zack Barresse]

The bastard smiles at me every time!!! So HE'S the narcissist!!! :D