Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default populate =IF(... dynamically in loop, possible?

I'm trying to calculate percentages that a column of values are of a
single cell value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(...
formula, is that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value &
, 0)"
next

But that won't work, I've tried a few other things, I want the sheet to
update when different values are entered into C6, with the percentages
if C6 is not empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default populate =IF(... dynamically in loop, possible?

hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(... formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet to update
when different values are entered into C6, with the percentages if C6 is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default populate =IF(... dynamically in loop, possible?

maybe...

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6<""""," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next
isabelle

Le 2016-05-19 18:40, isabelle a écrit :
hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(... formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet to update
when different values are entered into C6, with the percentages if C6 is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default populate =IF(... dynamically in loop, possible?

On 5/19/2016 4:22 PM, isabelle wrote:
maybe...
For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6<""""," & Cells(n, 1).Address & " /" &
Cells(6, 3).Address & ", 0)"
Next
isabelle


This worked perfectly, thank you so much!

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

Le 2016-05-19 18:40, isabelle a écrit :
hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" &
Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a
single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(...
formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value /
cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet
to update
when different values are entered into C6, with the percentages if C6
is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default populate =IF(... dynamically in loop, possible?


"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 & ",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with the
formula of the first cell

Peter T




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default populate =IF(... dynamically in loop, possible?

On 5/20/2016 1:12 AM, Peter T wrote:
"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 & ",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with the
formula of the first cell

Peter T


Is this the same as copying and pasting the formula? If so I tried that
and maybe I did it incorrectly but it didn't work. I have a single cell
I divide the whole column of numbers by and the reference to that cell
got incremented every time I pasted the formula to a new row. Maybe
there's a different method of pasting that I'm not aware of. I'll read
up on the "resize" command to see what you're doing. I never used or
heard of ".address" before now either, so thank you adn isabelle, I
appreciate the tutoring.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default populate =IF(... dynamically in loop, possible?

On 5/20/2016 1:12 AM, Peter T wrote:
"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & "
/" &
Cells(31, 4).Address & ", 0)"
Next


FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be
absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 &
",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with
the
formula of the first cell

Peter T


Is this the same as copying and pasting the formula? If so I tried
that and maybe I did it incorrectly but it didn't work. I have a
single cell I divide the whole column of numbers by and the reference
to that cell got incremented every time I pasted the formula to a new
row. Maybe there's a different method of pasting that I'm not aware
of. I'll read up on the "resize" command to see what you're doing. I
never used or heard of ".address" before now either, so thank you adn
isabelle, I appreciate the tutoring.


You need an *absolute* ref to the single cell used by all cell
formulas. The "$" symbol in cell refs denotes absolute or relative...

$A$1 = fully absolute
$A1 = column absolute, row relative
A$1 = column relative, row absolute
A1 = fully relative

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default populate =IF(... dynamically in loop, possible?


"Mike S" wrote in message
On 5/20/2016 1:12 AM, Peter T wrote:
"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 & ",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with the
formula of the first cell

Peter T


Is this the same as copying and pasting the formula? If so I tried that
and maybe I did it incorrectly but it didn't work.


It can be, but you need to be careful about absolute/relative addressing, ie
the $'s

Enter a formula in a cell (or even a value), put the cursor over the bottom
right corner and you should see a little-x. Drag it down or right.
Or if the formula is adjacent to a column of cells, double click the
little-x and it should "auto-fill"

I have a single cell I divide the whole column of numbers by and the
reference to that cell got incremented every time I pasted the formula to
a new row. Maybe there's a different method of pasting that I'm not aware
of. I'll read up on the "resize" command to see what you're doing. I never
used or heard of ".address" before now either, so thank you adn isabelle,
I appreciate the tutoring.


Not sure why the alternatives I suggested didn't work for you, for me they
recreated the exact same as your 34 to 81 loop, and just double checked.

Peter T


  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default populate =IF(... dynamically in loop, possible?

FWIW
My practice is to give cell refs local scope defined names, specifying
whether cols/rows are absolute or relative, and using the defined name
in formulas. For example...

A header row:
Name: "Sheet1!Hdr_Row"
RefersTo: =Sheet1!$1:$1

The cell above active cell:
Select A2
Name: "LastCell"
RefersTo: =Sheet1!A1

...where each name has local scope (sheet level) and so may be defined
and reused on other sheets in the same wb. Now you have a mechanism for
implementing dynamic totals, for example, across the bottom of a table
so totals auto-update when rows are inserted/deleted...

Range $A$1:$G$20 contains a table of values;
Range $A$1:$G$1 contains headers;
Range $A$20:$G$20 contains the following totals formula...
=SUM(Hdr_Row:LastCell)
..in all cells.

This suddenly makes your formula much more easier to understand what it
does than if it contained 'hard' cell refs, and also includes a
mechanism to be dynamic since "Hdr_Row" is fully absolute (fixed) and
"LastCell" is fully relative (floating) to the cell containing the
formula using those defined names.

Take this 1 step further and label cells A10:F10 of an invoice template
as follows...

ItemId Qty Description Price Amount Tax

...and name the cols as follows with cell A1 selected.

Name: "Sheet1!ItemId" RefersTo: =Sheet1!$A1
Name: "Sheet1!Qty" RefersTo: =Sheet1!$B1
Name: "Sheet1!ItemDesc" RefersTo: =Sheet1!$C1
Name: "Sheet1!Price" RefersTo: =Sheet1!$D1
Name: "Sheet1!Amount" RefersTo: =Sheet1!$E1
Name: "Sheet1!Tax" RefersTo: =Sheet1!$F1

Note that not all these names are really needed for this example, but
are included here deliberately by ws design practice.

Name some 'service' cells:
ColF: "Sheet1!Taxed"
Range $F$9: "Sheet1!TaxRate" and set its NumberFormat to %

Include as many invoice detail rows as you like fore the 'core'
template number of initial rows. (More rows can be inserted at any
time, which may even result a multi-page invoice)

Below the last detail row put totals for Amount.
Use the same concept for naming the headers row and "LastCell".

In the Amount col cells enter the following formula...
=IF(LEN(Qty),Price*Qty,"")

In the Tax col add a DV dropdown list "Y,N" if desired, or just use the
asterisk symbol to denote taxable items.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default populate =IF(... dynamically in loop, possible?

Oops.., pressed wrong keyboard combo before finishing...

FWIW
My practice is to give cell refs local scope defined names,
specifying whether cols/rows are absolute or relative, and using the
defined name in formulas. For example...

A header row:
Name: "Sheet1!Hdr_Row"
RefersTo: =Sheet1!$1:$1

The cell above active cell:
Select A2
Name: "LastCell"
RefersTo: =Sheet1!A1

..where each name has local scope (sheet level) and so may be defined
and reused on other sheets in the same wb. Now you have a mechanism
for implementing dynamic totals, for example, across the bottom of a
table so totals auto-update when rows are inserted/deleted...

Range $A$1:$G$20 contains a table of values;
Range $A$1:$G$1 contains headers;
Range $A$20:$G$20 contains the following totals formula...
=SUM(Hdr_Row:LastCell)
..in all cells.

This suddenly makes your formula much more easier to understand what
it does than if it contained 'hard' cell refs, and also includes a
mechanism to be dynamic since "Hdr_Row" is fully absolute (fixed) and
"LastCell" is fully relative (floating) to the cell containing the
formula using those defined names.

Take this 1 step further and label cells A10:F10 of an invoice
template as follows...

ItemId Qty Description Price Amount Tax

..and name the cols as follows with cell A1 selected.

Name: "Sheet1!ItemId" RefersTo: =Sheet1!$A1
Name: "Sheet1!Qty" RefersTo: =Sheet1!$B1
Name: "Sheet1!ItemDesc" RefersTo: =Sheet1!$C1
Name: "Sheet1!Price" RefersTo: =Sheet1!$D1
Name: "Sheet1!Amount" RefersTo: =Sheet1!$E1
Name: "Sheet1!Tax" RefersTo: =Sheet1!$F1

Note that not all these names are really needed for this example, but
are included here deliberately by ws design practice.

Name some 'service' cells:
ColF: "Sheet1!Taxed"


Range $F$9: "Sheet1!TaxRate" and set its NumberFormat to %
**You might want to put this in the 'Tax' calc row below detail rows**

Include as many invoice detail rows as you like fore the 'core'
template number of initial rows. (More rows can be inserted at any
time, which may even result a multi-page invoice)

Below the last detail row put totals for Amount.
Use the same concept for naming the headers row and "LastCell".

In the Amount col cells enter the following formula...
=IF(LEN(Qty),Price*Qty,"")

In the Tax col add a DV dropdown list "Y,N" if desired, or just use
the asterisk symbol to denote taxable items.


In the Amount col total row enter the following formula:
=SUM(Hdr_Row:LastCell) and name this cell "SubTotal" (local scope,
fully absolute)
Label the cell to the left "Sub Total"

Below that label the cell to the left "Tax", and enter the following
formula in the Amount col of that row:
=SUMIF(Taxed,"Y",OFFSET(Taxed,0,-1))*TaxRate

Below that label the cell to the left "Invoice Total" and enter the
following formula:
=SUM(SubTotal:LastCell)

Now if you did all correctly you'll have a reusable invoice template
with 9 rows for customizing however you want to design the top of your
invoice. Point is to show common use of various defined name cell refs!

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default populate =IF(... dynamically in loop, possible?

Hi Mike,

Am Thu, 19 May 2016 17:02:37 -0700 schrieb Mike S:

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


you don't need that loop. You can create the formula for the whole range
like Peter wrote.
And you don't have to check for 0. 0 divided by any number is always 0.
Try:
Range("D34:D81").Formula = "=B34/$D$31"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default populate =IF(... dynamically in loop, possible?


"Claus Busch" wrote in message
Hi Mike,

Am Thu, 19 May 2016 17:02:37 -0700 schrieb Mike S:

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


you don't need that loop. You can create the formula for the whole range
like Peter wrote.
And you don't have to check for 0. 0 divided by any number is always 0.
Try:
Range("D34:D81").Formula = "=B34/$D$31"



Indeed, but any number divided by 0 results in an error :)

another way in 2007 or later
Range("D34:D81").Formula = "=IFERROR(B34/$D$31,0)"

Peter T


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default populate =IF(... dynamically in loop, possible?

On 5/21/2016 3:45 AM, Peter T wrote:
"Claus Busch" wrote in message
Hi Mike,

Am Thu, 19 May 2016 17:02:37 -0700 schrieb Mike S:

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next


you don't need that loop. You can create the formula for the whole range
like Peter wrote.
And you don't have to check for 0. 0 divided by any number is always 0.
Try:
Range("D34:D81").Formula = "=B34/$D$31"



Indeed, but any number divided by 0 results in an error :)

another way in 2007 or later
Range("D34:D81").Formula = "=IFERROR(B34/$D$31,0)"

Peter T


Thanks guys. The sheets are done, all 31 of them, I'll keep these notes
for future reference, thanks again.
Mike

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically populate a Date pulldown menu? Arlen Excel Discussion (Misc queries) 1 July 24th 08 04:20 PM
Dynamically declare and populate variables Gary@Dwight Excel Programming 6 December 5th 07 07:27 PM
Dynamically populate spreadsheets in a workbook peashoe Excel Programming 4 August 8th 07 01:48 PM
Dynamically setting Dimensions in a VBA loop havocdragon Excel Programming 4 September 13th 06 08:34 AM
Populate workbook dynamically?? Dave Cleghorn Excel Programming 4 August 30th 06 04:20 PM


All times are GMT +1. The time now is 08:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"