Noob needs help comparing columns and returning counts 2

[Leeman73]

I am new to vba and have thrown myself in the deep end. I am using excel and am atttempting to write some code in VBA

I have two spreadsheets. The first spreadsheet has a column of ProductNames in them and they are all unique values, Variants. The second spreadsheet has 2 columns, the first column has multiple instances of ProductNames and an associated integer value in another column.

In the other worksheet, want to lookup at the values in the first column, find the exact match and then for every instance of a match, sum the value. Then I want to return the sum back to the adjacent cell in the first worksheet.

I neeed a bit of help on deciding the best way forward, i.e. loops, find, match etc.

Sub ProductQty()
'
' This macro looks up a cell value and finds a match in another column in another workseet.
' When a match is found the corresponding value in another column in the second worksheet is stored to a variable (count)
' For every match the value is then added to the count and the value is returned to a cell adjacent to the cell value in the first worksheet.
' The macro repeats this process until it reaches the end of the of the column in the first worksheet

Dim wkbk1name As Variant
Dim wkbk2name As Variant
Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Dim ActWkbk As Workbook
Dim ProductCode As Variant
Dim GetValue As Variant
Dim RunningTotal As Long
Dim Count As Integer

Set wkbk1 = ActiveWorkbook 'Assign worksheet

wkbk2name = Application.GetOpenFilename("Excel files, *.*") 'Open the second worksheet
If wkbk2name = False Then
Exit Sub
End If

Set wkbk2 = Workbooks.Open(Filename:=wkbk2name)

ThisWorkbook.Activate 'Activate the workbook with the ProductCode (unique values)

'Loop 1 should start here

ProductCode = ActiveSheet.Range("A2").Select 'Assign the Active cell value to a variable

RunningTotal = 0 'for each pass the running total is cleared

'loop2 should start here

wkbk2.Worksheets("Sheet1").Activate
GetValue = ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select 'Set the search range from A column, 2nd cell to the end of the column

If GetValue = ProductCode Then  'if a match is found then begin the running total

Count = ActiveCell.Offset(2, 0).Select
RunningTotal = RunningTotal + Count '

End If

ActiveCell.Offset(-2, -1).Select 'go back to the next cell below in the column

'when no more data in the column then return value to first worksheet and place RunningTotal into cell

'Loop 2 should end here

wkbk1.Worksheets("Sheet1").Activate
ActiveSheet.Range("B2").Select = RunningTotal
ActiveCell.Offset(-2, -1).Select 'go back 1 column and down one row to the next ProductCode

'Loop1 should end here.

End Sub

Also, if someone can tell me how to delete or edit a posted thread that would be useful.

 

[strongm]

I'd probably look at Excel's SUMIF function if I were you ...

 

[Leeman73]

I know this would be easy to do in Excel function but I wanted to try this in VBA.

 

[xlbo]

VBA:

MyTotalValue = application.sumif(Range,Criteria)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

tum ti tum ...

 

[xlbo]

Yes I know Mike - just showing how to do it in VBA!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Leeman73]

ok I have tried to simplify this now instead of opening and closing worksheets I have added the data to one worksheet.

I think the KISS (Keep it simple stupid) applies to me here.

I have 5 columns as shown below. I want to look in the first column (prdCde) and then look in the 4th column for an exact match for prdCde. I want to then Sum the sales value for every match and then return this value to the result column corresponding to the Prodcode from 1st Column

prdCde stock result prdCde sales
prod1 10 0 prod3 3
prod2 90 0 prod3 2
prod3 60 0 prod7 1
prod4 50 0 prod9 7
prod5 40 0 prod7 5
prod6 20 0 prod3 3
prod7 90 0 prod6 4
prod8 20 0 prod7 3
prod9 30 0


The result should look like this.

prdCde stock result prdCde sales
prod1 10 0 prod3 3
prod2 90 0 prod3 2
prod3 60 8 prod7 1
prod4 50 0 prod9 7
prod5 40 0 prod7 5
prod6 20 4 prod3 3
prod7 90 9 prod6 4
prod8 20 0 prod7 3
prod9 30 7



Basically I need help with assigning the correct ranges and using the MyTotalValue = application.sumif(Range,Criteria) as mentioned by xlbo

 

[strongm]

Dim mycell As Range
Dim LookupList As Range
Dim SummableData As Range
Dim ResultCell As Range
Dim LookupCell As Range
Dim DataLastRow As Long


DataLastRow = Range("D").End(xlDown).Row
Set LookupList = Range("D2" & DataLastRow)
Set SummableData = Range("E2:E" & DataLastRow)
For Each ResultCell In Range("C2:C" & Range("A:A").End(xlDown).Row)
Set LookupCell = Range("A" & ResultCell.Row)
ResultCell.Value = WorksheetFunction.SumIf(LookupList, LookupCell, SummableData)
Next

 

[Leeman73]

StrongM your a legend!!!!

Thanks so much. You saved me much headache. I really appreciate your assistance and the others who replied to this post and provided valuable information.