Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Hi There,
Is it possible to do the following? 1. In column B from row 6 downwards: (***up to last row in column C*** (no empty rows)) WRITE THE FORMULA: (for B6:) =IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOK UP (C6;ACCOUNTS;1;FALSE)) ("increasing" for B7 as copying down the relative formula manually) 2. In column A from row 6 donwards: WRITE THE FORMULA: (for A6:) =IF(IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VL OOKUP(C6;ACCOUNTS;1;FALSE))=C6;"";IF(ISERROR(VLOOK UP(C6;ACCOUNTS;1;FALSE));B5;VLOOKUP(C6;ACCOUNTS;1; FALSE))&C6) Small step for guru? ... too big step for me! So, Any help really appreciated! :o) Sige |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Sige,
Dim i As Long For i = 6 To Cells(Rows.Count, "C").End(xlUp).Row Cells(i, "A").Formula = _ "=IF(IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE)), " & _ "B5,VLOOKUP(C6,ACCOUNTS,1,FALSE))=C6,""""," & _ "IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE))," & _ "B5,VLOOKUP(C6,ACCOUNTS,1,FALSE))&C6)" Cells(i, "B").Formula = _ "=IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE)),B5,VLOO KUP(C6,ACCOUNTS,1,FALSE))" Next i I had to use , as a separator. You might need ; you might not try tyhem both. -- HTH RP (remove nothere from the email address if mailing direct) "Sige" wrote in message oups.com... Hi There, Is it possible to do the following? 1. In column B from row 6 downwards: (***up to last row in column C*** (no empty rows)) WRITE THE FORMULA: (for B6:) =IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOK UP (C6;ACCOUNTS;1;FALSE)) ("increasing" for B7 as copying down the relative formula manually) 2. In column A from row 6 donwards: WRITE THE FORMULA: (for A6:) =IF(IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VL OOKUP(C6;ACCOUNTS;1;FALSE) )=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B 5;VLOOKUP(C6;ACCOUNTS;1;FA LSE))&C6) Small step for guru? ... too big step for me! So, Any help really appreciated! :o) Sige |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Dim lastrow as long, numrows as long
lastrow = cells(rows.count,3).End(xlup).row numrows = lastrow - 5 Range("B6").Resize(numrows,1).Formula = _ "=IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE))" & _ ",B5,VLOOKUP(C6,ACCOUNTS,1,FALSE))" Range("A6").Resize(numrows,1).Formula = _ "=IF(IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE)), B5" & _ ",VLOOKUP(C6,ACCOUNTS,1,FALSE))=C6,"""",IF(ISERROR " & _ "(VLOOKUP(C6,ACCOUNTS,1,FALSE)),B5,VLOOKUP(" & _ "C6,ACCOUNTS,1,FALSE))&C6)" -- Regards, Tom Ogilvy "Sige" wrote in message oups.com... Hi There, Is it possible to do the following? 1. In column B from row 6 downwards: (***up to last row in column C*** (no empty rows)) WRITE THE FORMULA: (for B6:) =IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOK UP (C6;ACCOUNTS;1;FALSE)) ("increasing" for B7 as copying down the relative formula manually) 2. In column A from row 6 donwards: WRITE THE FORMULA: (for A6:) =IF(IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VL OOKUP(C6;ACCOUNTS;1;FALSE) )=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B 5;VLOOKUP(C6;ACCOUNTS;1;FA LSE))&C6) Small step for guru? ... too big step for me! So, Any help really appreciated! :o) Sige |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Yes...what is your question? What do you want to do -- olas ----------------------------------------------------------------------- olasa's Profile: http://www.excelforum.com/member.php...fo&userid=1776 View this thread: http://www.excelforum.com/showthread.php?threadid=39055 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Just a heads up:
This would never adjust the cell references in the formula (contrary to the OP's specification - probably overlooked) and the comma is used with the Formula property regardless of the regional settings (not semi-colon). -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Sige, Dim i As Long For i = 6 To Cells(Rows.Count, "C").End(xlUp).Row Cells(i, "A").Formula = _ "=IF(IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE)), " & _ "B5,VLOOKUP(C6,ACCOUNTS,1,FALSE))=C6,""""," & _ "IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE))," & _ "B5,VLOOKUP(C6,ACCOUNTS,1,FALSE))&C6)" Cells(i, "B").Formula = _ "=IF(ISERROR(VLOOKUP(C6,ACCOUNTS,1,FALSE)),B5,VLOO KUP(C6,ACCOUNTS,1,FALSE))" Next i I had to use , as a separator. You might need ; you might not try tyhem both. -- HTH RP (remove nothere from the email address if mailing direct) "Sige" wrote in message oups.com... Hi There, Is it possible to do the following? 1. In column B from row 6 downwards: (***up to last row in column C*** (no empty rows)) WRITE THE FORMULA: (for B6:) =IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOK UP (C6;ACCOUNTS;1;FALSE)) ("increasing" for B7 as copying down the relative formula manually) 2. In column A from row 6 donwards: WRITE THE FORMULA: (for A6:) =IF(IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VL OOKUP(C6;ACCOUNTS;1;FALSE) )=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B 5;VLOOKUP(C6;ACCOUNTS;1; FA LSE))&C6) Small step for guru? ... too big step for me! So, Any help really appreciated! :o) Sige |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing formulas ...
Hi Bob & Tom,
Many Many Many Thx for your replies!!! Bob: the formulas are written where they should be. Though the formula is absolute! While Tom's solution was actually where I was after: It pastes the formula in a "relative" way. I owe you ... already sooo much! :o)))) Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing formulas | New Users to Excel | |||
Writing formulas between sheet | Excel Discussion (Misc queries) | |||
writing if formulas | Excel Worksheet Functions | |||
Writing Formulas | Excel Worksheet Functions | |||
Problem writing formulas - please, please help! | Excel Programming |