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;VLOOKUP
(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;VLOOKUP(C6;ACCOUNTS;1;FALSE))=C6;"";IF(ISERROR(VLOOKUP(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

Yes...what is your question? What do you want to do?

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,VLOOKUP(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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1122469265.216340.220720@o13g2000cwo.googlegroups.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;VLOOKUP
> (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;VLOOKUP(C6;ACCOUNTS;1;FALSE)
)=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOKUP(C6;ACCOUNTS;1;FA
LSE))&C6)
>
> Small step for guru? ... too big step for me!
>
> So, Any help really appreciated! :o)
> Sige
>

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OvhYe9qkFHA.2180@TK2MSFTNGP15.phx.gbl...
> 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,VLOOKUP(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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> news:1122469265.216340.220720@o13g2000cwo.googlegroups.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;VLOOKUP
> > (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;VLOOKUP(C6;ACCOUNTS;1;FALSE)
> )=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOKUP(C6;ACCOUNTS;1;
FA
> LSE))&C6)
> >
> > Small step for guru? ... too big step for me!
> >
> > So, Any help really appreciated! :o)
> > Sige
> >
>
>

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" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1122469265.216340.220720@o13g2000cwo.googlegroups.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;VLOOKUP
> (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;VLOOKUP(C6;ACCOUNTS;1;FALSE)
)=C6;"";IF(ISERROR(VLOOKUP(C6;ACCOUNTS;1;FALSE));B5;VLOOKUP(C6;ACCOUNTS;1;FA
LSE))&C6)
>
> Small step for guru? ... too big step for me!
>
> So, Any help really appreciated! :o)
> Sige
>

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 ***