May look simple but very important to me

MSG · May 13th, 2002, 01:17 PM

Mr. Excel,

Thank you for your lovely site and I have a question which might be very simple to you but knowing that is very important to me.

In cell # A1 for example I will enter a number say 1000
In cell # B1 for example I will enter another number say 50
And I want excel somehow with a formula or something to divides 1000 by 50 and then replicates the figure in cell # B1 which is 50 twenty (that is from 1000/50) times across the sheet.
So the end result to this would be

Cell # A1 = 1000
Cell # B1 = 50
Cells # Cl to V1 = 50

Thanks, and I will be waiting for you reply

MSG

Asala42 · May 13th, 2002, 01:24 PM

How about this copied from C1 as far to the right as you want:

=IF(COLUMN(B1)-1<=($A$1/$B$1),B1,"")

(keep in mind there are a finite number of columns however)
Adam

MSG · May 13th, 2002, 01:44 PM

Thanks for the prompt answer, I have copied your solution and couldn’t come up with the data I required. Can you please give me more details on where I should apply this if formula I mean to which cell in order for me to get the result motioned in my original message

Thanks again

Chris Davison · May 13th, 2002, 01:51 PM

Put Adam's formula into cell C1 and then drag it across.

It works for me.

Asala42 · May 13th, 2002, 01:53 PM

Hey again,

A1 = 1000
B1 = 50
C1 has the formula: =IF(COLUMN(B1)-1<=($A$1/$B$1),$B$1,"")

Copy this formula to the right. This will show C1 through V1 contain the number 50 (the value in B1). That is 20 columns with the value of B1 in it.

Did I misunderstand your goal somewhere?

Adam

Jay Petrulis · May 13th, 2002, 07:36 PM

Here is a VBA solution, if you prefer.

Code:

Sub test()
Dim x As Integer

x = Range("A1")  Range("B1")
Range("C1").Resize(1, x) = Range("B1")

End Sub

EDIT: Please note that there is only one backslash in the line that calculates x. Reply with quote to see it correctly.

HTH,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-13 21:54 ]

MSG · May 16th, 2002, 01:17 PM

I really appreciate all your replies, the macro worked OK but I couldn’t have the formula working although I am following what you had stated earlier. I don’t know what I am missing or doing wrong???

Asala42 · May 16th, 2002, 01:28 PM

Hey again,

I'm not sure what might be going on incorrectly. Maybe we're just beating the cat against the wall since you already have a vba solution - but if you want to know what's wrong with the formula you might want to post exactly what you're seeing.

BTW - actually I like cats

Adam

May 13th, 2002, 01:51 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Put Adam's formula into cell C1 and then drag it across. It works for me. __________________ :: Pharma Z - Family drugstore ::

May 13th, 2002, 07:36 PM	#6
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Here is a VBA solution, if you prefer. Code: Sub test() Dim x As Integer x = Range("A1") Range("B1") Range("C1").Resize(1, x) = Range("B1") End Sub EDIT: Please note that there is only one backslash in the line that calculates x. Reply with quote to see it correctly. HTH, Jay [ This Message was edited by: Jay Petrulis on 2002-05-13 21:54 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 01:17 PM	#1
MSG New Member Join Date: May 2002 Location: Saudi Arabia Posts: 3	Mr. Excel, Thank you for your lovely site and I have a question which might be very simple to you but knowing that is very important to me. In cell # A1 for example I will enter a number say 1000 In cell # B1 for example I will enter another number say 50 And I want excel somehow with a formula or something to divides 1000 by 50 and then replicates the figure in cell # B1 which is 50 twenty (that is from 1000/50) times across the sheet. So the end result to this would be Cell # A1 = 1000 Cell # B1 = 50 Cells # Cl to V1 = 50 Thanks, and I will be waiting for you reply MSG

May 13th, 2002, 01:24 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,306	How about this copied from C1 as far to the right as you want: =IF(COLUMN(B1)-1<=($A$1/$B$1),B1,"") (keep in mind there are a finite number of columns however) Adam

May 13th, 2002, 01:44 PM	#3
MSG New Member Join Date: May 2002 Location: Saudi Arabia Posts: 3	Thanks for the prompt answer, I have copied your solution and couldn’t come up with the data I required. Can you please give me more details on where I should apply this if formula I mean to which cell in order for me to get the result motioned in my original message Thanks again

May 13th, 2002, 01:53 PM	#5
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,306	Hey again, A1 = 1000 B1 = 50 C1 has the formula: =IF(COLUMN(B1)-1<=($A$1/$B$1),$B$1,"") Copy this formula to the right. This will show C1 through V1 contain the number 50 (the value in B1). That is 20 columns with the value of B1 in it. Did I misunderstand your goal somewhere? Adam

May 16th, 2002, 01:17 PM	#7
MSG New Member Join Date: May 2002 Location: Saudi Arabia Posts: 3	I really appreciate all your replies, the macro worked OK but I couldn’t have the formula working although I am following what you had stated earlier. I don’t know what I am missing or doing wrong???

May 16th, 2002, 01:28 PM	#8
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,306	Hey again, I'm not sure what might be going on incorrectly. Maybe we're just beating the cat against the wall since you already have a vba solution - but if you want to know what's wrong with the formula you might want to post exactly what you're seeing. BTW - actually I like cats Adam