Replacing sumproducts with VBA code

[da_reynolds]

Hey All,

First post on the forum, usually find what I want in other peoples questions, hope you can help me out.

I have a list of times which need to be entered into a sheet for each contractor against the work they have done. Originally I used a sum product to pull the value out based on 2 unique codes, one for the time and shift, and one for the contractor and work. However, with over 180000 cells this takes for ever. I was hoping to replace it using the find function to search for one unique code, return the associated row, and the other for the column, then put the value into my list into this matrix.

Diagramatically:

A|F|50hr
B|G|40hr
C|H|10hr
D|I|5hr
E|J|3hr

into

....A...B...C...D...E
F..50.................
G .....40.............
H ..........10........
I ................5....
J ....................3

This is simplistic, it won't be a diagonal matrix of values.

I can't get the code i've written to work properly, any help would be greatly appreciated.

Sub Populate_sheet()
Dim SearchCell As Range
Dim FoundCell1 As Range
Dim FoundCell2 As Range
Dim SearchRange1 As Range
Dim SearchRange2 As Range
Dim i As Integer
Dim y As Integer
Dim x As Integer
i = 1
Set SearchRange1 = Worksheets("Timesheet").Range("M3:DA3")
Set SearchRange2 = Worksheets("Timesheet").Range("A7:A3000")
For Each cell In Worksheets("Calcs").Range("I3:I30")
 
    Set SearchCell = Worksheets("Calcs").Cells(i, 1)
    Set FoundCell1 = SearchRange1.Find(SearchCell, LookIn:=xlValues)
    y = FoundCell1.Column
    Set SearchCell = Worksheets("Calcs").Cells(i, 2)
    Set FoundCell2 = SearchRange2.Find(SearchCell, LookIn:=xlValues)
    x = FoundCell2.Row
 
    Worksheets("Calcs").Cells(i, 9).Value = Worksheets("Timesheet").Cells(x, y)
    i = i + 1
Next
End Sub

Thanks!

 

[nightcrawler23]

Do you have A|F|50hr in different cells???

 

[nightcrawler23]

Whats the problem with above code?

 

[da_reynolds]

Sorry, yes, they are in different cells.

The code keeps returning the error:

"Object variable or With block variable not set"

It's indicating the y=FoundCell.Column to be the problem line.

 

[Fazza]

Hi,

Are you set on using code?

What about without code using a qeury table or pivot table?

regards

 

[Rick Rothstein]

You should specify the other arguments for the Find method because this method has a "memory". So, the last time you used Find (whether in code or from the menu bar), if your search was for xlWhole (the cell must contain only the text you are searching for), then this time you are still searching with xlWhole. Or if the last time you used Find you specified the search should be case sensitive, then the search is performed this time in a case sensitive manner. I don't know if one of the past settings is the root cause of your current problem, but I suspect it might be.

 

[nightcrawler23]

The error may be bcoz it cannot find the searchcell in the searchrange

Try this method,

Sub temp()
Dim cl As Range
Dim myRow, myCol As Integer
Dim searchRow, searchCol As Range
Set searchRow = Range("G2:G6") '[COLOR="SeaGreen"]G2:G6 = a,b,c,d,e[/COLOR]
Set searchCol = Range("H1:L1")  'H1:L1 = F,G,H,I,J
For Each cl In Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    myRow = WorksheetFunction.Match(cl, searchRow, 0)
    myCol = WorksheetFunction.Match(cl.Offset(0, 1), searchCol, 0)
    searchRow.Cells(1, 1).Offset(myRow - 1, myCol) = cl.Offset(0, 2)
Next cl
End Sub

A1:A5 = A,B,C,D,E
B1:B5 = F,G,H,I,J
C1:C5 = 10,20,30,40,50

H2:L6 WILL BE YOUR OUTPUT TABLE

 

[da_reynolds]

Thanks for the suggestions, i'll give them a try and see how I go.

I would have used Pivot tables except that there are other functions that then run off the generated table. Making them work with the pivot table was tricky and clunky.