PDA

View Full Version : Solved: Solved:Totaly stuck



james123
01-02-2007, 06:32 PM
Hi Guys,

I am totally stuck on this question, I don?t even know where to start. Basically I have 2 workbooks of data one with currently nearly 6000 records (named Data07) and one with currently over 400 records (named Sorted07).

Both Sorted07 and Data07 have one column that is named the same ?iLM Serial Number? Basically I need a Macro that looks at cell A2 in Sorted07 (which is the iLM Serial Number) then searches down the list in Data07 column D (also the iLM Serial Number) if it finds an exact match it copies the matched row (A:M) from Data07 and inserts all the values into the sorted07 workbook in column F2:R2. If there is no match (which sometimes there wont be) or if a match is found, it simply moves on to A3, and so on, doing the same until the end of the data in Sorted07. There will obviously be a few blank rows when no match is found!

Does this make sense? Would if the rows are sorted some how? Or should I just use a vlookup? Or can it be done with a cool macro?

Thanks in advance for your help guys! :beerchug:

James

XLGibbs
01-02-2007, 06:50 PM
To summarize... Sorted07 is the source list from which to search Data07. and A:M of Data07 to return to F:R of Sorted07 on matched records.

This was just typed now, so it may need some cleanup, but should be okay. Pretty basic search and destroy code I have used many times.




Dim wbData as Workbook, wbSorted as Workbook
Dim wsD as Worksheet, wsS as worksheet
Dim rngLook as Range, rngFound as Range, rngID as range
Application.Screenupdating = False
Application.EnableEvents = False

Set wbData = Workbooks("Data07")
Set wbSorted = Workbooks("Sorted07")
Set wsD = wbData.Sheets("Sheet1") '<=== make sure the sheet name is correct
Set wsS = wbSorted.Sheets("Sheet1") '<====sheet name

Set rngID = wsS.Range(cells(2,1),cells(rows.count,1)).end(xlup) 'column A of Sorted

Set rngLook = wsD.Range(cells(2,4),cells(rows.count,4)).end(xlup) 'column D of Data

Dim c as Range

for each c in rngID
Set rngFound = rngLook.Find(c)
If Not rngFound is nothing then
c.Offset(0,2).Resize(1,13).Value = rngFound.Resize(1,13).Value
'c is column D, so move 2 columns to the right (column F) and make the
'next 13 columns = A:M of the matched record in Data..

End if
Next c

Set wbSorted = Nothing : Set wbData = Nothing : Set rngID = nothing : Set rngFOund = Nothing
Set rngLook = Nothing:Set wsD = nothing:Set wsS = nothing
Application.Screenupdating = True
Application.EnableEvents = True
End Sub

james123
01-02-2007, 07:17 PM
Run-time error '1004':

Method 'Range' of object'_Worksheet' failed

It stops on:

Set rngLook = wsD.Range(Cells(2, 4), Cells(Rows.Count, 4)).End(xlUp) 'column D of Data

I dont recognize the error

XLGibbs
01-02-2007, 07:25 PM
In this case it means that the range is not qualified...are you sure the data is column D of that sheet and the sheet name is identified correctly above where I Set wsD = Sheets("Sheet1")

Since the rngID Set statement is identical, I can only assume that in this line, it is erroring out because a) column D of the specified sheet as NO data. b) the specified sheet name exists in the other workbook, but does not contain the data...check the sheet name. If the sheet name is correct..

You can try

Set rngLook = wsD.Range("D2:D65536").end(xlup)

as an alternative

You can also directly specify the range by identifying the exact address of the range---perhaps .. wsD.Range("D2:D402") ?


Edit: I just tried the code, and the line is correctly written...double check the sheet name and that it is the right one with data.

james123
01-02-2007, 08:08 PM
Hi, Still no luck, maybe you can see what i am doing wrong, i have checked all you said, the sheet name is Sheet 1 on both workbooks, see pic below.

http://the-music.info/james/RTW/Assets/blogold/error2.bmp

XLGibbs
01-02-2007, 08:13 PM
Did you try changing the code to represent the actual range needed?


Set rngLook = wsD.Range("D2:D402") 'set to the correct last row number on Data07, Sheet1 columnD


THe code is identical to the "Set rngID" syntax, so it is not a syntax error.

If you want to email me the workbook, feel free, but if I create two workbooks, name them Data07, Sorted07 and put data in the required columns, the code works---not sure why the error would be coming up.

james123
01-02-2007, 08:19 PM
whats your e-mail address?

Or both workbooks are on the end of the first post

Or e-mail me and i will reply

Nexos.Temp(at)itx.com.au - Change "(at)" to "@"

XLGibbs
01-02-2007, 08:35 PM
Have you tried what I said? I will try and download the two files in the meantime.

james123
01-02-2007, 08:39 PM
Yes and it now get stuck on the line above,

Set rngID = wsS.Range(cells(2,1),cells(rows.count,1)).end(xlup) 'column A of Sorted

Should i change this one as well?

XLGibbs
01-02-2007, 08:50 PM
Yeah, I am fixing it, but so far, it runs fine and only gets one match so I am diagnosing why only 1 match is found.

james123
01-02-2007, 08:59 PM
Thanks, yep I got it to work as well, and found why only one match.

There are lots of spaces after the codes in "sorted07", im guessing this is why, is there anyway to get it to ignore the spaces.

Or another macro to get rid of all the spaces?!

XLGibbs
01-02-2007, 09:09 PM
Oye.

Yeah, I fixed the extra spaces in the code.

I have to figure out my brain cramp on these range Set's. I have another set of code that errored out in another post. I probably have a parenthesis out of whack or something stupid...

Anyways, 324 matchs. Sorted07 updated with code attached.

You can remove the attachments from your post, and I will delete this attachment once you have it .

Sub dirty()

Dim wbData As Workbook, wbSorted As Workbook
Dim wsD As Worksheet, wsS As Worksheet
Dim rngLook As Range, rngFound As Range, rngID As Range
Dim c As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

Set wbData = Workbooks("Data07.xls")
Set wbSorted = Workbooks("Sorted07.xls")
Set wsD = wbData.Sheets("Sheet1") '<=== make sure the sheet name is correct
Set wsS = wbSorted.Sheets("Sheet1") '<====sheet name

Set rngID = Range("A2:A406") 'column A of Sorted
MsgBox rngID.Address

Set rngLook = wsD.Range("D2:D5880") 'column D of Data
MsgBox rngLook.Address

For Each c In rngLook
c = Replace(c, " ", "")
Next c
For Each c In rngID
c = Replace(c, " ", "")
Next c

x = 0
For Each c In rngID
Set rngFound = rngLook.Find(Trim(c))
If Not rngFound Is Nothing Then
c.Offset(0, 5).Resize(1, 13).Value = rngFound.Resize(1, 13).Value
'c is column A, so move 5 columns to the right (column F) and make the
'next 13 columns = A:M of the matched record in Data..
x = x + 1
End If
Next c
MsgBox x & ":Matches Found"
Set wbSorted = Nothing: Set wbData = Nothing: Set rngID = Nothing: Set rngFound = Nothing
Set rngLook = Nothing: Set wsD = Nothing: Set wsS = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

james123
01-02-2007, 09:18 PM
Thank you, awsome :D

XLGibbs
01-02-2007, 09:20 PM
Thank you, awsome :D

No problem. Sorry about the brain fades. It "looked" right, and didn't syntax out on me, but was wrong. TOo much time writing SQL has corrupted by VBA mind. ;)

Just let me know if you want me to delete the attachment in my post, and you should delete yours if there is sensitive data..