Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
I have some worksheets that use a Vlookup statement to get information from
other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Do you really need to check every cell, or can the rows/columns be
restricted to a more sensible range? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Well, there really isn't a way for me t restrict the number of rows of data.
This has been a work in progress for the last month or so for me. Here is what I have. I have 4 worksheets that are given us by another department. The 4 worksheets are .csv files from a batch job and those csv files contain information about a companies users. The number of users a company has and how those users have been set up determines the number of rows each of the 4 files will have. By the way, the 4 files all contain similar but still different data and the amount of data (rows) can be different depending on how the users were set up. Anyway, my spreadsheet needs to look at those 4 files and import or use the data on them to come up with some useable information. Up until recently we were doing a manual copy/paste from each data sheet into the master sheet and then the function I showed previously filled in the rest. I've since automated the import process but would like to enchance what I have just a little more. I'm not sure I answred your question... I can not retrict the number of rows I can look at. The company I'm working on today only has 157 rows of data but like I said we had one yesterday with 1010 so It's all over the board. We could do something to determine the number of row the data sheets have though, right? If it would help for you to see what I have I could send you a sample of my master and data files. "Bob Phillips" wrote: Do you really need to check every cell, or can the rows/columns be restricted to a more sensible range? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Dim rng as Range, rng1 as Range
set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
I appreciate your reply time. I'm not sure I understand how this code will
work for me. How does the returned value get to the right cell and how does the code loop until there is no more data to use? "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Looping is for Chumps <g Don't do it if you don't have to.
The data gets to the return cell because I put your formula in the cell - actually every cell in column A for every used cell in column D all in one command. (You have the formula correctly set up so the 2 for the row will adjust properly for each row it is entered). I then replace the formula with the value it displays. (Again all in one command) To summarize, I do this all in one fell swoop by determinging how many rows are utilized in column D. (as you asked) -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I appreciate your reply time. I'm not sure I understand how this code will work for me. How does the returned value get to the right cell and how does the code loop until there is no more data to use? "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Why not test it on a copy of your data to get a feel for what it does.
Obviously, I only have your description and formula to go on, so I may have misunderstood, but I believe it will do what you want. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Looping is for Chumps <g Don't do it if you don't have to. The data gets to the return cell because I put your formula in the cell - actually every cell in column A for every used cell in column D all in one command. (You have the formula correctly set up so the 2 for the row will adjust properly for each row it is entered). I then replace the formula with the value it displays. (Again all in one command) To summarize, I do this all in one fell swoop by determinging how many rows are utilized in column D. (as you asked) -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I appreciate your reply time. I'm not sure I understand how this code will work for me. How does the returned value get to the right cell and how does the code loop until there is no more data to use? "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Wow Tom! That really worked better than I hoped for!! MUCH thanks! One issue
did arise from the solution though. I took your code and used it everywhere I needed to but I can't apply it in one area of the spreadsheet and if I can't use it here then the rest doens't work either. Here is the formula I'm using: rng5.Formula = "=IF(D2=D1,"",IF(ISERROR(VLOOKUP($D2,'XXX DeleteCodes'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2, 'XXX DeleteCodes'!$1:$65536,2,FALSE)))" rng5.Formula = rng5.Value When the formula was on the worksheet the D2=D1 incremented to D3=D2 then D4=D3 and so on. Well now that it's a single statment embedded in code it doesn't work dor obvious reasons. Can you help me with this too? "Tom Ogilvy" wrote: Why not test it on a copy of your data to get a feel for what it does. Obviously, I only have your description and formula to go on, so I may have misunderstood, but I believe it will do what you want. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Looping is for Chumps <g Don't do it if you don't have to. The data gets to the return cell because I put your formula in the cell - actually every cell in column A for every used cell in column D all in one command. (You have the formula correctly set up so the 2 for the row will adjust properly for each row it is entered). I then replace the formula with the value it displays. (Again all in one command) To summarize, I do this all in one fell swoop by determinging how many rows are utilized in column D. (as you asked) -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I appreciate your reply time. I'm not sure I understand how this code will work for me. How does the returned value get to the right cell and how does the code loop until there is no more data to use? "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move from a function statement to VBA code?
Hi Tom,
Could you possible help me with the other part of the issue? The code you gave me works great but unless I can get this last part to work then I can't use your code. I really appreciate your help! "Tom Ogilvy" wrote: Why not test it on a copy of your data to get a feel for what it does. Obviously, I only have your description and formula to go on, so I may have misunderstood, but I believe it will do what you want. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Looping is for Chumps <g Don't do it if you don't have to. The data gets to the return cell because I put your formula in the cell - actually every cell in column A for every used cell in column D all in one command. (You have the formula correctly set up so the 2 for the row will adjust properly for each row it is entered). I then replace the formula with the value it displays. (Again all in one command) To summarize, I do this all in one fell swoop by determinging how many rows are utilized in column D. (as you asked) -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I appreciate your reply time. I'm not sure I understand how this code will work for me. How does the returned value get to the right cell and how does the code loop until there is no more data to use? "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng = Range(Cells(2,"D"),Cells(rows.count,"D").End(xlup) ) set rng1 = rng.offset(0,-3) rng1.Formula = "=IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"""",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE))" ' to remove the formulas and replace with the value returned ' uncomment the next line 'rng1.Formula = rng1.Value Adjust the rng1.Formula line(s) to fix the word wrap. Note the """" in place of "" -- Regards, Tom Ogilvy "hshayh0rn" wrote in message ... I have some worksheets that use a Vlookup statement to get information from other worksheets. Right now I have the statement below repeated for 1000 rows. I'd like to remove these statement and complete the work entirely in VBA code. One of the reasons for this is to make the speadsheet a little quicker in some instances and more dynamic in others. Generally we don't have the need for 1000 rows BUT we never truly know how many rows we need and just yesterday we needed 1010 rows which meant I had to send out a revised sheadsheet to that particular user. Could anyone like to help with with the code I need to get the same results I would get below? The statement below appears in cell A2 on a worksheet call "consolidate sheet" and is repeated for 1000 row but as I mentioned I need it to populate the cells in column A of the "consolidated sheet" until there is no more data to get. =IF(ISERROR(VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)),"",VLOOKUP($D2,'XXX User Report'!$1:$65536,2,FALSE)) Thank you for your help in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code - Find & Move | Excel Discussion (Misc queries) | |||
VBA Code - Find & Move | Excel Discussion (Misc queries) | |||
How do I set up an if statement to move to next column each time | Excel Programming | |||
UserForm_Layout code Me.Move 0, 0 don't work | Excel Programming | |||
How to move files from the code | Excel Programming |