retrieving data in a list according to 3 criteria

[choubix]

hi again,

I have 2 worksheets: 1 and 2
worksheet 1 is a "list" of transactions with data from column A to BU

worksheet 2 has data from A to H.
in worksheet 2 I have 3 criterias that I need to find in the list located in worksheet 1, retrieve some data and copy it in worksheet 2.

I doubt a lookup wil do the trick since I have 3 criteria (and the list is not "sorted").

what would be a simple and not too heavy procedure (since the file is already really heavy) to locate the data in 1 according to the 3 criteria please?

thanks

[Bob Phillips]

Post the workbook.

[choubix]

not sure my boss would appreciate... ;)
and the whole thing is 4mo.

[Bob Phillips]

Well there is far too little detail in your post to work on.

Can't you create a summy workbook that has the format/structure that will allow us to see what you want?

[choubix]

Hi Xld,

I'll create a dummy file tomorrow with fake data then.

I hoped to work on the workbook tonight from home but even my email address doesnt seem to allow the file in! :(

see you tomorrow on the forum ;)

+++

[RonMcK]

choubix,

Can you save some space by using a zip, tar, or winRAR program? Zip/WinZip/PKZip and the like are the preferred compression utilities around these parts, as I recall. At least .zip if the file extension you can upload to this site.

Many ISPs limit email attachments to 1mb. In addition, I know where I work limits email attachments to 1 mb for files being sent outside; 2 or 3 mb on mail that stays inside.

HTH,

[choubix]

hi guys,

I managed to come up with this solution:


Sub ELN_Restructuring2()
Dim i As Integer, j As Integer
Dim wsFix As Worksheet, ws1 As Worksheet
Set wsFix = Worksheets("R_ELN_Fixings")
Set ws1 = Worksheets("1")
For i = 1 To wsFix.Range("A:A").CurrentRegion.Rows.count
For j = 1 To ws1.Range("V:V").CurrentRegion.Rows.count


If wsFix.Cells(i + 1, 3).Value = ws1.Cells(j, 22).Value And _
wsFix.Cells(i + 1, 2).Value = ws1.Cells(j, 10).Value And _
wsFix.Cells(i + 1, 5).Value = ws1.Cells(j, 36).Value And _
wsFix.Cells(i + 1, 6).Value = ws1.Cells(j, 1).Value Then 'use i+1 to skip the header

'get the values from sheet "1" and prints them in "R_ELN_Fixings"
wsFix.Cells(i + 1, 10) = ws1.Cells(j, 29).Value
wsFix.Cells(i + 1, 11) = ws1.Cells(j, 30).Value
wsFix.Cells(i + 1, 12) = ws1.Cells(j, 31).Value
wsFix.Cells(i + 1, 13) = ws1.Cells(j, 32).Value
wsFix.Cells(i + 1, 14) = ws1.Cells(j, 33).Value
wsFix.Cells(i + 1, 15) = ws1.Cells(j, 34).Value
wsFix.Cells(i + 1, 16) = ws1.Cells(j, 37).Value
wsFix.Cells(i + 1, 17) = ws1.Cells(j, 38).Value
wsFix.Cells(i + 1, 18) = ws1.Cells(j, 39).Value
wsFix.Cells(i + 1, 19) = ws1.Cells(j, 40).Value
wsFix.Cells(i + 1, 20) = ws1.Cells(j, 41).Value
End If
Next j
Next i
MsgBox "Please Check : R_ELN_Fixings worksheet", vbInformation
End Sub


i doubt it is the best way to handle the job but it works.
do you think there is a better way to handle the task please?

[Bob Phillips]

Why do you have two separate, nested For ... Next loops?

The copy could be done in blocks. I notice that the copied data doesn't include cells 35 and 36, is this correct?

[choubix]

cells 35 and 36 are ignored on purpose indeed

I can have between [0,infinite[ records in wsFix and between [1;3000[ in ws1. i am retrieving info in ws1 using data in wsFix (like a vlookup on 4 criterias)

[Bob Phillips]

cells 35 and 36 are ignored on purpose indeed

You can still improve it



'get the values from sheet "1" and prints them in "R_ELN_Fixings"
ws1.Cells(j, 29).Resize(, 6).cop wsFix.Cells(i + 1, 10)
ws1.Cells(j, 37).Resize(, 5).cop wsFix.Cells(i + 1, 16)



I can have between [0,infinite[ records in wsFix and between [1;3000[ in ws1. i am retrieving info in ws1 using data in wsFix (like a vlookup on 4 criterias)

That hasn't helped me because I don't understand it (apart from the last bit which I already understood).

[choubix]

hello,

thanks Xld
resize is very useful!

I had to change the selection of cells to be copied from ws1 to wsFix.
I came up with this code (truncated a bit)

ws1.Cells(j, 29).Resize(, 6).cop wsFix.Cells(i + 1, 10)
ws1.Cells(j, 41).cop wsFix.Cells(i + 1, 16)
ws1.Cells(j, 8).cop wsFix.Cells(i + 1, 17)

it returns an error 438 (object doesnt support this method or property)

any idea why?


is it possible to change the cell format at this level or shall I do it differently (the workbook sometimes return errors like: "too many cells format" which make people have a few headaches...)

thanks

[Bob Phillips]

Unforyunately you can see I just typed that not tested it. I have no idea how I transposed wsFix to ws1, but worse still, instaed of copy I used cop. Just add a y to the two liens and you should be good to go.

[choubix]

just seeing your message now xld

i changed it earlier and it works fine with Copy instead of cop indeed.

as for the cells format: do you think I should specifiy it at the range level or at the cell level while the macro is runnign the loop??

there are too many differents formats in the workbook making it sometimes hard to perform so basic actions :(

thanks :)

[mdmackillop]

XLD's code is OK if your cells contain values, but if they contain formulae, you'll need to PasteSpecial Values or use

wsfix.Cells(i + 1, 10).Resize(, 6).Value = ws1.Cells(j, 29).Resize(, 6).Value
wsfix.Cells(i + 1, 16).Resize(, 5).Value = ws1.Cells(j, 37).Resize(, 5).Value

[Bob Phillips]

as for the cells format: do you think I should specifiy it at the range level or at the cell level while the macro is runnign the loop??

there are too many differents formats in the workbook making it sometimes hard to perform so basic actions :(

Which format are you referring to?