| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula in macro or in sheet/cells?
Hi,
I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
modify this idea to suit
Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
Thanks for the template, Don - what is frng?
/Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
Hi again Don
I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
thanks Don
My range is C1:E115 - but it could be changing over time. /Heine Don Guillett wrote: If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
try
Sub makeformulae() Set frng = Range("f16:f" & Cells(Rows.Count, "C").End(xlUp).Row) With frng .Formula = "=vlookup(a9,raab1,3,0)" 'or '.Formula = "=vlookup(a9,$c$1:$e$1000,3,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... thanks Don My range is C1:E115 - but it could be changing over time. /Heine Don Guillett wrote: If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
thx Don,
I will be back in a few days to try it out. Thanks for your help so far. And I hope you will stand by for further assistance. /Heine Don Guillett wrote: try Sub makeformulae() Set frng = Range("f16:f" & Cells(Rows.Count, "C").End(xlUp).Row) With frng .Formula = "=vlookup(a9,raab1,3,0)" 'or '.Formula = "=vlookup(a9,$c$1:$e$1000,3,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... thanks Don My range is C1:E115 - but it could be changing over time. /Heine Don Guillett wrote: If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
Hi again Don
My formula now looks exactly like this: Sub makeformulae() Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng Formula = "=if(a9="";"";if(isna(vlookup(a9;råb1;3;false));"" ;(vlookup(a9;råb1;3;false))))" Formula = .Value 'changes to a value if desired End With End Sub As I understand it this should set my defined formula active in all the cells in column O from row 9 - that is it should return the result of the formula in the entire column (almost) - but nothing happens. Any thoughts? And what can I use this line for? Formula = .Value 'changes to a value if desired /Heine Don Guillett wrote: try Sub makeformulae() Set frng = Range("f16:f" & Cells(Rows.Count, "C").End(xlUp).Row) With frng .Formula = "=vlookup(a9,raab1,3,0)" 'or '.Formula = "=vlookup(a9,$c$1:$e$1000,3,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... thanks Don My range is C1:E115 - but it could be changing over time. /Heine Don Guillett wrote: If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula in macro or in sheet/cells?
when you have a with statement you must use the period before what pertains
to that with. You apparently did not copy and modify mine. So, try putting a . before formula Formula ..Formula -- Don Guillett SalesAid Software "Heine" wrote in message ps.com... Hi again Don My formula now looks exactly like this: Sub makeformulae() Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng Formula = "=if(a9="";"";if(isna(vlookup(a9;råb1;3;false));"" ;(vlookup(a9;råb1;3;false))))" Formula = .Value 'changes to a value if desired End With End Sub As I understand it this should set my defined formula active in all the cells in column O from row 9 - that is it should return the result of the formula in the entire column (almost) - but nothing happens. Any thoughts? And what can I use this line for? Formula = .Value 'changes to a value if desired /Heine Don Guillett wrote: try Sub makeformulae() Set frng = Range("f16:f" & Cells(Rows.Count, "C").End(xlUp).Row) With frng .Formula = "=vlookup(a9,raab1,3,0)" 'or '.Formula = "=vlookup(a9,$c$1:$e$1000,3,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... thanks Don My range is C1:E115 - but it could be changing over time. /Heine Don Guillett wrote: If you can't think this out it might be easier if you just tell me what your range (rabbi) is. -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Hi again Don I have problems understanding this line: Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) what is "a"? - where do I fill in my data in this line exactly? /Heine Don Guillett wrote: just a name. Name it whatever you like such as Myrng or Crng or xxxxx -- Don Guillett SalesAid Software "Heine" wrote in message ups.com... Thanks for the template, Don - what is frng? /Heine Don Guillett wrote: modify this idea to suit Sub makeformulae() Set frng = Range("c16:c" & Cells(Rows.Count, "a").End(xlUp).Row) With frng .Formula = "=vlookup(a16,$a$16:$b$22,2,0)" .Formula = .Value'changes to a value if desired End With End Sub -- Don Guillett SalesAid Software "Heine" wrote in message oups.com... Hi, I would like to put my lookup formula in the macro instead of in the cells. =IF(A9="";"";IF(ISNA(VLOOKUP(A9;raab1;3;FALSE));"" ;(VLOOKUP(A9;raab1;3;FALSE)))) Any suggestion as to how this is done most easily? What are the implications of this? Is it at all smart? The idea is that I can use the macro in many sheets instead of copying the formulas from sheet to sheet. Thanks in advance! /Heine |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Macro Formula revision? | Excel Worksheet Functions | |||
Linear Mode
