Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Hello all, I'm trying to write up a macro for excel to streamline some things I have to do regularly, but this one is giving me a bit of trouble. I don't think it is too complicated, I could probably do it in C++ easily, but VBA is always iffy with me. Luckily, with some help from others, I got most of it written but I am having a small problem still. First some background: I have two workbooks, each with 1 sheet in them. 'WorkbookA.xls|Sheet1' is more or less static list but it may have additional entries from month to month (think of it as a 'flag it' list). It essentially contains information about things. The names are all in column A (lets say down to 100 for simplicity's sake). The locations are in column B. Code: -------------------- Example: 1) Column A Column B 2) Person A CHARLOTTE 3) Person B JACKSONVILLE 4) Person C HALIFAX -------------------- 'WorkbookB.xls|Sheet1' has a rather large list of transactions which get assigned an ID in column A by a macro we already have in place. The names are in column C. The locations are in column D Code: -------------------- Example: 1) A B C D 2) 123 Contact A Person A CHARLOTTE (More info) 3) 123 Contact A Person B JACKSONVILLE (More info) 4) 225 Contact B Person A CHARLOTTE (More info) 5) 225 Contact B Person D LOS ANGELES (More info) 6) 334 Contact C Person C HALIFAX (More info) -------------------- However, Workbook B does not account for any of the 'flagged' items in workbook A. In the above Workbook B example, rows 2,3,4, and 6 should be flagged. So I need this macro to take the data from column A in the first workbook, and run it through Workbook B (column C). If it were to find a match, it should change Workbook B (Column A) to another ID value (lets say 5000). For simplicity, only name matching was done at first, here is the code thus far: Code: -------------------- Dim rngA as Range, rngB as Range, rng as Range Dim cell as Range, sAddr as String with workbooks("WorkbookA.xls").Worksheets("Sheet1") set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown)) End with With workbooks("WorkbookB.xls").Worksheets("Sheet1") set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown)) End with for each cell in rngA set rng = rngB.Find(What:=cell.Value, _ After:=rngB(rngB.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then sAddr = rng do if rng.offset(0,1).Value = _ cell.offset(0,1).Value then cell.offset(0,-2).Value = 5000 end if set rng = rngB.FindNext(rng) loop until rng.Address = sAddr End if Next -------------------- I get errors on the following line: cell.offset(0,-2).Value = 5000 Any ideas? Thanks for your help. -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
try including a carriage return symbol _ on the previous line.
"mazzarin" wrote: Hello all, I'm trying to write up a macro for excel to streamline some things I have to do regularly, but this one is giving me a bit of trouble. I don't think it is too complicated, I could probably do it in C++ easily, but VBA is always iffy with me. Luckily, with some help from others, I got most of it written but I am having a small problem still. First some background: I have two workbooks, each with 1 sheet in them. 'WorkbookA.xls|Sheet1' is more or less static list but it may have additional entries from month to month (think of it as a 'flag it' list). It essentially contains information about things. The names are all in column A (lets say down to 100 for simplicity's sake). The locations are in column B. Code: -------------------- Example: 1) Column A Column B 2) Person A CHARLOTTE 3) Person B JACKSONVILLE 4) Person C HALIFAX -------------------- 'WorkbookB.xls|Sheet1' has a rather large list of transactions which get assigned an ID in column A by a macro we already have in place. The names are in column C. The locations are in column D Code: -------------------- Example: 1) A B C D 2) 123 Contact A Person A CHARLOTTE (More info) 3) 123 Contact A Person B JACKSONVILLE (More info) 4) 225 Contact B Person A CHARLOTTE (More info) 5) 225 Contact B Person D LOS ANGELES (More info) 6) 334 Contact C Person C HALIFAX (More info) -------------------- However, Workbook B does not account for any of the 'flagged' items in workbook A. In the above Workbook B example, rows 2,3,4, and 6 should be flagged. So I need this macro to take the data from column A in the first workbook, and run it through Workbook B (column C). If it were to find a match, it should change Workbook B (Column A) to another ID value (lets say 5000). For simplicity, only name matching was done at first, here is the code thus far: Code: -------------------- Dim rngA as Range, rngB as Range, rng as Range Dim cell as Range, sAddr as String with workbooks("WorkbookA.xls").Worksheets("Sheet1") set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown)) End with With workbooks("WorkbookB.xls").Worksheets("Sheet1") set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown)) End with for each cell in rngA set rng = rngB.Find(What:=cell.Value, _ After:=rngB(rngB.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then sAddr = rng do if rng.offset(0,1).Value = _ cell.offset(0,1).Value then cell.offset(0,-2).Value = 5000 end if set rng = rngB.FindNext(rng) loop until rng.Address = sAddr End if Next -------------------- I get errors on the following line: cell.offset(0,-2).Value = 5000 Any ideas? Thanks for your help. -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Hi
rngA is column 1 so cell.offset(0,-2) is an error. Do you mean rng.offset(0,-2), so that the 5000 is in workbookB? regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
I have attempted that, and I just tried it again, and here is the code just to be su Code: -------------------- If Not rng Is Nothing Then sAddr = rng Do If rng.Offset(0, 1).Value = _ cell.Offset(0, 1).Value Then _ cell.Offset(0, -2).Value = 5000 End If Set rng = rngB.FindNext(rng) Loop Until rng.Address = sAddr End If -------------------- which results in the error: End If without block If pointing to line: Code: -------------------- cell.Offset(0, -2).Value = 5000 End If <-- *this* -------------------- -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Hi rngA is column 1 so cell.offset(0,-2) is an error. Do you mean rng.offset(0,-2), so that the 5000 is in workbookB? regards Paul That must be it, it runs! But it seemingly continues on to infinity - stuck on Loop Until rng.Address = sAddr More debugging needed it seems... -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Okay, after stepping through the code a bit I've established that i runs all the way down to the bottom. On its way, it sets sAddr a Customer A. Code ------------------- Loop Until rng.Address = sAddr ------------------- Then, it jumps back to Code ------------------- If rng.Offset(0, 1).Value = _ cell.Offset(0, 1).Value Then rng.Offset(0, -2).Value = 5000 End If Set rng = rngB.FindNext(rng) Loop Until rng.Address = sAddr ------------------- and doesn't change any values on the worksheet, nor does it update it own variables. This continues constantly, no matter how many times I step through it Going to work on it a bit more -- mazzari ----------------------------------------------------------------------- mazzarin's Profile: http://www.excelforum.com/member.php...fo&userid=3218 View this thread: http://www.excelforum.com/showthread.php?threadid=51931 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Hi
Do you want to put the update inside your For loop as you have found the cell you want (so there is no point finding its address) e.g. Dim rngA as Range, rngB as Range, rng as Range Dim cell as Range with workbooks("WorkbookA.xls").Worksheets("Sheet1") set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown)) End with With workbooks("WorkbookB.xls").Worksheets("Sheet1") set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown)) End with for each cell in rngA set rng = rngB.Find(What:=cell.Value, _ After:=rngB(rngB.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then if rng.offset(0,1).Value = _ cell.offset(0,1).Value then cell.offset(0,-2).Value = 5000 end if End if Next Cell regards Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Aha, found (part of) the problem, the loop was looking in the wrong location! rng.Offset(0, 0) cell.offset(0,0) (instead of 0,1 in both) make it look in the right location and then update the ID, however it does not move on to check for person B, it only updates the ID for both instances of person A (and continues to loop infinitely) -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Dim rngA as Range, rngB as Range, rng as Range
Dim cell as Range, sAddr as String with workbooks("WorkbookA.xls").Worksheets("Sheet1") set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown)) End with With workbooks("WorkbookB.xls").Worksheets("Sheet1") set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown)) End with for each cell in rngA set rng = rngB.Find(What:=cell.Value, _ After:=rngB(rngB.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then sAddr = rng.address do if rng.offset(0,1).Value = _ cell.offset(0,1).Value then rng.offset(0,-2).Value = 5000 end if set rng = rngB.FindNext(rng) loop until rng.Address = sAddr End if Next -- Regards, Tom Ogilvy "mazzarin" wrote in message ... Okay, after stepping through the code a bit I've established that it runs all the way down to the bottom. On its way, it sets sAddr as Customer A. Code: -------------------- Loop Until rng.Address = sAddr -------------------- Then, it jumps back to Code: -------------------- If rng.Offset(0, 1).Value = _ cell.Offset(0, 1).Value Then rng.Offset(0, -2).Value = 5000 End If Set rng = rngB.FindNext(rng) Loop Until rng.Address = sAddr -------------------- and doesn't change any values on the worksheet, nor does it update its own variables. This continues constantly, no matter how many times I step through it. Going to work on it a bit more. -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
That isn't correct. It has already made the match on the values in rngA and
rngB. You said you would then like to check that the values to the right also match. This if did that. If you change it as you indicated, then there is no need in having the test. I have posted some corrected (although untested) code. Your problem with saddr should be fixed in that. -- Regards, Tom Ogilvy "mazzarin" wrote in message ... Aha, found (part of) the problem, the loop was looking in the wrong location! rng.Offset(0, 0) cell.offset(0,0) (instead of 0,1 in both) make it look in the right location and then update the ID, however it does not move on to check for person B, it only updates the ID for both instances of person A (and continues to loop infinitely) -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bit of a VBA problem.
Got it! Thanks. -- mazzarin ------------------------------------------------------------------------ mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186 View this thread: http://www.excelforum.com/showthread...hreadid=519316 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |