PDA

View Full Version : macro in excel



writwika
09-16-2014, 12:36 AM
can someone plz help me out to write a macro for the format below:
= 43.29+67.86
= 111.15+67.86
= 179.01+67.86.... same operation to be done in the other columns with different values. it would be a grt help!!!

Bob Phillips
09-16-2014, 01:19 AM
What exactly is the macro supposed to look at and what is it supposed to do?

writwika
09-16-2014, 01:29 AM
What exactly is the macro supposed to look at and what is it supposed to do?

step 1: it should add up 43.29+67.86. which is = 111.15.
step 2: with 111.15 it should add 67.86. so, 111.15+67.86=179.01
step 3 again with 179.01, 67.86 is to be added. so, 179.01+67.86=246.87......

for next column 53.61+46.24=99.85
=146.09+46.24
=192.33+46.24.... for other columns different values are there.

GTO
09-16-2014, 04:15 AM
My dearest writwika,

We cannot look over your shoulder and see the workbook you have in front of you.

"step 1: it should add up 43.29+67.86. which is = 111.15."

Okay, but pray tell, where are we to find these numbers to add? We cannot be a great help if you do not express from wherein the values are to be found...

I see that it is your first thread. Welcome to the forum, vbax is great! We just need you to 'draw' a clearer 'picture' of what you are wanting to do. For instance, "I want to add all numerical values in column A, and plunk the return in cell n. Then the same thing for columns B through n.", would give us a clue as to the goal...

Mark

writwika
09-16-2014, 08:58 AM
a random variable is taken. to this we add 11.47. again, we add 11.47 to the result. this process goes on 5500 times. 11.47 is fixed. please help me out. please do say if u have a doubt still.

Bob Phillips
09-16-2014, 09:28 AM
No wonder we didn't understand. 11.47 was pretending to be 67.86. Can't say it is fully explained yet, but here is one attempt

=A1*11.47*5500

writwika
09-16-2014, 08:43 PM
67.86 is to be added to a random variable in the 1st cell. now from the next row i want to keep on adding 67.86 to the previous result. this goes on 5500 times. it will look like:
let us take the random variable as 11.47.
= 11.47+67.86
= 79.33+67.86
=147.19+67.86... now can u get a picture of it???

Aussiebear
09-17-2014, 02:06 AM
.. now can u get a picture of it???

So can we assume that you mean that cell A1 + 67.86, and fill down for 5500 rows?

snb
09-17-2014, 02:34 AM
To what purpose ??

Aussiebear
09-17-2014, 03:34 AM
To what purpose ??

you out remember the bloke in Canada who shifted towns???

snb
09-17-2014, 03:44 AM
So, he's back ?

Bob Phillips
09-17-2014, 04:16 AM
So can we assume that you mean that cell A1 + 67.86, and fill down for 5500 rows?

Nah, he means that some random number in a cell gets multiplied by 67.86, then multiplied by 67.86, then gets ... 5500 times. But he doesn't say whether that random number is input by someone/something, whether he wants the code to generate it, what the bounds of that random number are, whether he wants that number multiplied by 67.86 5500 times whenever that random number appears, whether he wants a button for the user to press every time, or what. Despite asking for clarification the OP only seems to be able to regurgitate - sounds that this may be homework.

writwika
09-17-2014, 05:01 AM
the random variable we select should be any number less than 100 and user does NOT input it. the cell no changes in every step. A1+67.86. then A2+67.86. A3+67.86....this goes on 5500 times automatically. so the execution should stop at A5500.

Aussiebear
09-17-2014, 12:06 PM
@ writwika, why didn't you say so initially as this would have cleared up the confusion surrounding this thread

writwika
09-18-2014, 05:55 AM
plz provide the macro for it. it is urgent.

snb
09-18-2014, 06:46 AM
If it's urgent hire a software specialist.

Kenneth Hobs
09-18-2014, 07:34 AM
Sub ken()
Range("A1").Value = Application.MRound(Rnd() * 99.99, 0.01)
Range("A2:A5500").Formula = "=A1+67.86"
Range("A2:A5500").NumberFormat = "0.00"
End Sub

writwika
09-18-2014, 09:18 AM
thanks for the reply but the code is not working. showing runtimme error.

GTO
09-18-2014, 09:39 AM
thanks for the reply but the code is not working. showing runtimme error.

I copied Kenneth's suggestion verbatim, plunked it in a Standard Module, and it runs fine.

Kenneth Hobs
09-18-2014, 10:00 AM
Most likely you are running a version older than 2010. Replace the mround function with this one.

Sub ken() Range("A1").Value = Round(Rnd() * 99.99, 2)
Range("A2:A5500").Formula = "=A1+67.86"
Range("A2:A5500").NumberFormat = "0.00"
End Sub

When it errors, you can use Debug to see where it errored. You can also step through code by pressing F8 to debug line by line.

writwika
09-18-2014, 08:25 PM
it is showing compile error: invalid use of property, indicating NumberFormat.

Kenneth Hobs
09-18-2014, 08:34 PM
Are you sure you don't have two lines of code on one line like post #20 has? I would edit it if I could. The forum has been running the first two lines into one line when I paste code here lately. I try to edit my posts but I miss a run on like that sometimes. Howsoever, that is a different compile error.

It should be:

Sub ken()
Range("A1").Value = Round(Rnd() * 99.99, 2)
Range("A2:A5500").Formula = "=A1+67.86"
Range("A2:A5500").NumberFormat = "0.00"
End Sub



Could be that some quote marks were not translated properly when you copied and pasted the code into a Module.

Attach your file so that we can see what is going on.

What version of Excel are you using?

writwika
09-18-2014, 10:43 PM
it is microsoft excel 2003

writwika
09-18-2014, 10:57 PM
ya it runs. bt can we display the output like =43.29+67.86 in A1.
in A2 =111.15+67.86. #111.15=43.29+67.86
in A3 =179.01+67.86 . #179.01=111.15+67.86
this goes on till A5500.

GTO
09-18-2014, 11:09 PM
Please post a workbook with what the output should look like. Not all 5500 rows mind you, just about ten rows so we can get a clear picture.

Mark

writwika
09-18-2014, 11:53 PM
The workbook looks like:



A
B
C
D
E


1
39.37+57.43
43.29+67.86
48.35+76.24
53.61+46.24
59.25+36.29


2
=96.8+57.43
=111.15+67.86
=124.59+76.24
=99.85+46.24
=95.54+36.29


3
=154.23+57.43

=179.01+67.86
=200.83+76.24
=146.09+46.24
=131.83+36.29


4
=211.66+57.43
=246.87+67.86
=277.07+76.24
=192.33+46.24
=168.12+36.29


5
=269.09+57.43
=314.73+67.86
=353.31+76.24
=238.57+46.24
=204.41+36.29


6
=326.52+57.43
=382.59+67.86
=429.55+76.24
=284.81+46.24
=240.7+36.29


7
=383.95+57.43
=450.45+67.86
=505.79+76.24
=331.05+46.24
=276.99+36.29


8
=441.38+57.43
=518.31+67.86
=582.03+76.24
=377.29+46.24
=313.28+36.29


9
=498.81+57.43
=586.17+67.86
=658.27+76.24
=423.53+46.24
=349.57+36.29


10
=556.24+57.43
=654.03+67.86
=734.51+76.24
=469.77+46.24
=385.86+36.29

Kenneth Hobs
09-19-2014, 06:42 AM
I fear that you have still not fully defined what you want. Howsoever, this will do what your post #26 requested less the first random number. It also allows more flexibility so you can change the number of rows and columns to fill. I also attached an XLS file. It includes buttons to clear the usedrange and to run the fill as many times as you like, quickly.


Sub ww_Test()
Dim i As Long, a() As Variant
'ww "A", 10, 62.5
a() = Array(57.43, 67.86, 76.24, 46.24, 36.29)
For i = 1 To 5
ww ColumnLetter(CInt(i)), 10, CDbl(a(i - 1))
Next i
End Sub


Sub ww(col As String, endColRow As Long, con As Double)
Dim arrayS() As String, arrayD() As Double, d As Double
Dim x As Long, conS As String
ReDim arrayS(1 To endColRow)
ReDim arrayD(1 To endColRow)

d = Round(Rnd() * 99.99, 2)
conS = Format(con, "0.00")

arrayD(1) = d + con
For x = 2 To endColRow
arrayD(x) = arrayD(x - 1) + con
Next x

arrayS(1) = Format(d, "0.00") & "+" & conS
For x = 2 To endColRow
arrayS(x) = "'=" & Format(arrayD(x - 1), "0.00") & "+" & conS
Next x

Range(col & "1:" & col & endColRow).Value = WorksheetFunction.Transpose(arrayS)
Columns(col).AutoFit
End Sub


Function ColumnLetter(ColumnNum As Integer) As String
ColumnLetter = Split(Cells(1, ColumnNum).Address, "$")(1)
End Function

snb
09-19-2014, 08:01 AM
or


Sub M_snb()
ReDim sp(1 To 20, 1 To 5)
Randomize
sn = Array(Round(100 * Rnd, 2), Round(100 * Rnd, 2), Round(100 * Rnd, 2), Round(100 * Rnd, 2), Round(100 * Rnd, 2))

For j = 1 To UBound(sp)
sp(j, 1) = "'=" & FormatNumber(sn(0) + (j - 1) * 57.43, 2) & "+57.43"
sp(j, 2) = "'=" & FormatNumber(sn(1) + (j - 1) * 67.86, 2) & "+67.86"
sp(j, 3) = "'=" & FormatNumber(sn(2) + (j - 1) * 76.24, 2) & "+76.24"
sp(j, 4) = "'=" & FormatNumber(sn(3) + (j - 1) * 46.26, 2) & "+46.26"
sp(j, 5) = "'=" & FormatNumber(sn(4) + (j - 1) * 36.29, 2) & "+36.29"
Next

Cells(10, 1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

writwika
09-19-2014, 08:24 AM
yes it works. but as i am new to macros can u plz tell me from where the 1st rowz data 24.92, 46.55, 78.10, 62.83, 64.24 is taken. actually i want those nos. to be 39.37, 43.29, 48.35, 53.61, 59.25.

writwika
09-19-2014, 08:41 AM
it is taking random values but i want them to be fixed. can it be done?

snb
09-19-2014, 08:46 AM
Please, read again: http://www.vbaexpress.com/forum/showthread.php?50735-macro-in-excel&p=315352&viewfull=1#post315352.

writwika
09-19-2014, 08:58 AM
i understand. first it was told to take a random value. but now i am told to take fixed values. plz do help.

snb
09-19-2014, 11:00 AM
We can't help if you change your point of view every second post.

You received an abundance of valuable suggestions, even solutions.
Now you must be able to adapt those to every (often switching) 'requirement'.
The help we offer you is to enable you to create your own solutions in your own company.
If you need a professional software developer because of lacking the skills yourself, you'd better hire one.

writwika
09-19-2014, 07:15 PM
this is the last request. no more changes. if possible please do give a solution with the permanent values. thank you in advance.

Kenneth Hobs
09-19-2014, 08:32 PM
I fear that because you are not taking the time to examine what the code does, every time you need a very simple tweak, you will be back yet again. We don't mind helping but it seems you have not even tried to understand what the code does. It is like this. We can do the fishing for you but if you don't even try baiting the hook, you are bound to never catch a fish on your own.

You provided no feedback saying which of the last two solutions best worked for you. I therefore can only guess what might suffice.

snb tends to write short code and powerful code but not so simple to understand for a beginner. Using his code, one simply needs to replace one line of code where he built an array of random numbers. It should be easy to understand that replacing variable values for constant values in that array is trivial. One can use my code by simply adding an array with the constant values in a similar way.

'Option Explicit

Sub M_snb2()
ReDim sp(1 To 20, 1 To 5)
Randomize
sn = Array(39.37, 43.29, 48.35, 53.61, 59.25)

For j = 1 To UBound(sp)
sp(j, 1) = "'=" & FormatNumber(sn(0) + (j - 1) * 57.43, 2) & "+57.43"
sp(j, 2) = "'=" & FormatNumber(sn(1) + (j - 1) * 67.86, 2) & "+67.86"
sp(j, 3) = "'=" & FormatNumber(sn(2) + (j - 1) * 76.24, 2) & "+76.24"
sp(j, 4) = "'=" & FormatNumber(sn(3) + (j - 1) * 46.26, 2) & "+46.26"
sp(j, 5) = "'=" & FormatNumber(sn(4) + (j - 1) * 36.29, 2) & "+36.29"
Next

Cells(10, 1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

writwika
09-19-2014, 09:59 PM
i regret for ur dissapointment. @Kennenth Hobs, sir, ur code worked for me better. now i understand what the code does. the data is fixed and not random. i am trying my best to get the result with the help of ur macro.

writwika
09-19-2014, 10:48 PM
it is done. thank you so much for your help and guidance. and sorry to disappoint u all.