• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to multiply small list by UserEntry value?

C

cash_flow_pro

New Member
Hello.


I'm a VBA newbie & today I'm trying to write my first ever program - but I'm not sure exactly how I should approach multiplying a (very small) excel list by a user entry value .. (or if indeed I should have it in excel at all, or just include into the code?)


I've got my UserEntry Value .. but after that I'm unsure .. does anyone any idea's?


Thanks.
 
Hi,


Why don't you consider uploading your file so we can have better understanding of your data.


To do the same please follow the link:

http://chandoo.org/forums/topic/posting-a-sample-workbook


And perhaps, you may want to explain it bit more clearly like what is your multiplication factor, any logic/criteria behind multiplication etc.


Kaushik
 
Hi kaushik03, and thanks for your reply!


I'm not sure I know what a multiplication factor is, but I'll try to explain:


It's really very simple (and my first ever VBA), I'm just trying to learn, not make the meanest baddest fastest script in the world!


So .. I've got a recipe list in excel, item names in cells a1:a7 and values per person in cells b1:b7


I've asked how many people we're feeding with the following code (which to be honest I only partly understand as have borrowed and adapted from somewhere else!)


Sub NoOfCovers()

' find out how many covers

Dim UserEntry As Variant

Dim Msg As String

Msg = "How many people are attending?"

UserEntry = InputBox(Msg)

If UserEntry = "" Then Exit Sub

End Sub


And now what I'd like to do is write some further script that multiplies each value in cells b1:b7 by the UserEntry value collected above.


Does this make sense?


I'd also like to keep all the procedures seperate (as I'm still learning) and make a main procedure which will call the Sub NoOfCovers() and then action the multiplication.


Output wise, I'd like the multiplied numbers to land in cells c1:c7 for the time being (as am still unsure if they should be in excel or if the code should just know the portions per person ..


Any idea's?


Thanks
 
[pre]
Code: 
Sub NoOfCovers()
' find out how many covers
Dim UserEntry As Variant
Dim Msg As String
Dim c As Range
Msg = "How many people are attending?"
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
For Each c In Worksheets("Sheet1").Range("B1:B7")
c.Offset(, 1).Value = c.Value * UserEntry
Next
End Sub
[/pre]
 
Hi, cash_flow_pro!


Excel is a very power and flexible tool for data handling and transforming/calculating/viewing/charting and a lot more of ings... So, even if it'd be more simple and efficient to do what you want to achieve with Excel formulas, it's valid and worthy your effort to build your first VBA code.


Try inserting the following code between yours:

-----

[pre]
Code: 
Option Explicit

Sub NoOfCovers()
'...
Dim I As Integer
'...
For I = 1 To 7
With ActiveSheet
.Cells(I, 3).Value = .Cells(I, 2).Value * UserEntry
End With
Next I
'...
End Sub
[/pre]
-----


Good luck with VBA, and just advise if any issue.


Regards!


PS: Oops!... it seems as if I overstepped on Hui... would he be alright?
 
I just adjust your code a bit. Try this:


Sub NoOfCovers()

' find out how many covers

Dim UserEntry As Variant

Dim lstrow As Long

Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim Msg As String


k = 1

j = 1


lstrow = ThisWorkbook.Worksheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Row


Msg = "How many people are attending?"

UserEntry = InputBox(Msg)

If UserEntry <> "" Then

For i = 1 To lstrow

Cells(k, 3).Value = Cells(j, 2).Value * UserEntry

k = k + 1

j = j + 1


Next i

Else

Exit Sub


End If


End Sub
 
Hey Guys! Thanks so much for your help, it's very much appreciated!


Hui, your code is absolutly perfect - except that I want the multiplied values to be dropped into c1:c7 (so I can keep the original data 'clean') - how would I action that?


Sir JB7, nice to make your acquantance as well :) And thank you very much for your kind encouragement! I've just opened the VBA book this week, and to be honest it's all quite daunting! I've read half the PowerProgramming 2007 book and don't feel at all ready, but I'm trying anyway - hopefully a little bit of practise will send me in the right direction :)


I'm not sure I follow where I should try to input your code though?


Thanks!
 
Thats what my formula does does?

The .Offset(,1) shifts the location to Column C instead of B

I changed the formula after I initially posted it to fix that, you must have downloaded the inital version
 
Hi, cash_flow_pro!


Your full code with my version should be like this:

-----

[pre]
Code: 
Option Explicit

Sub NoOfCovers()
' find out how many covers
Dim UserEntry As Variant
Dim Msg As String
Dim I As Integer
Msg = "How many people are attending?"
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
For I = 1 To 7
With ActiveSheet
.Cells(I, 3).Value = .Cells(I, 2).Value * UserEntry
End With
Next I
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
haha, I was just coming back to say that @Hui! wows - this is amazing, thank you so very much! now I'm onto my next step at least :) :)


It's interesting how different your approaches are to achieve the same goal - I can see the For-Next loop (is that the right name?) but otherwise you've gone different ways ..


.. and I hardly understand half of them .. so something tells me i'm barely looking at the tip of the iceburg where VBA is concerned .. exciting & scary all at the same time!


Thanks again you guys, I seriously don't think I could be doing this if wasn't for the chandoo.org!


Thanks.
 
Hi, cash_flow_pro!


The multiple approaches are due to Excel flexibility, it's up to you to choose your own path. As a humble advice, read a lot, copy & past more, edit much more, get stuck against the wall, review, try again.


It might be helpful too if you try to use the built-in macro recorder (Programmer tab, Code group, Recording Macro icon) or just the same icon at left bottom of Excel window. You'd experiment with both absolute and relative cell referencing methods (icon near first described) and check the different generated codes.


Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!
 
Many thanks SirJB7!


I've tried some recording & some writing, and have played with the relative cell referencing, but figure it's also in the practice, so am pleased I have my personal prject to be working on :)


Something tells me it's going to take a BIG while before I'll be fluent though, am halfway through the PowerProgramming book and still don't think I've even touched the tip of the iceburg!!


Thankfully it's not particulary nice weather here in Europe this summer, so I'm not missing too much outdoors fun!


Thanks again, you guys are stars!
 
Hi, cash_flow_pro!

Building your own learning path it'd be the better way. And BIG it's only a matter of time and dedication.

Regards!

PS: BTW, if you want to change your European summer by my South American winter, just let me know and send tickets to IAmBoredOfThisWinter@WantSummerToArrive.com.now
 
I would swap this terrential downpour in Amsterdam for South America any day dude!!!


A learning path sounds like a good idea though .. tell me, what exactly do you mean?


Also, about your code .. I'm getting a run-time error '13: type mismatch on the 4th from bottom line starting .cells .. ?


Also, I don't understand that line beginning .cells at all .. what's that doing?


And .. how does it know where the values to multiply are?


Thanks!
 
Hi, cash_flow_pro!


Your learning path means finding the mix between all available sources in each proportion as you want.

You're at the half of PP2007 book? finish it. Like this forums? read it daily. Want to experiment? record your own macros. Ready to give a try? build your own manually entered subs or functions.

Your own path? Choose which ones, in what order, with what dedication to each.


The code works on ActiveSheet, so for running it you should have the data sheet activated or selected. If it doesn't solve the issue, consider uploading the sample file.


About what ".cells" does, let us analyze the code:

-----

[pre]
Code: 
For I = 1 To 7
With ActiveSheet
.Cells(I, 3).Value = .Cells(I, 2).Value * UserEntry
End With
Next I
[/pre]
-----


The For...Next varies integer (-32768 thru 32767) variable I values from 1 thru 7.

The With...EndWith used with an object, in this case ActiveSheet (that is your data worksheet), and lets omit the object qualification within it: that's to say that ".Cells..." is the same as "ActiveSheet.Cells..." if you hadn't use the With...EndWith block.

So code actually executed is:

ActiveSheet.Cells(I,3).Value = ActiveSheet.Cells(I,2).Value * UserEntry

That means: take the content of Value property of Cell(I,2) of current worksheet, multiply it by the content of UserEntry variable and store it in Cell(I,3) of same worksheet.

What is Cells(X,Y)? It's the cell located at row X, column Y.


Hope it helps.


Regards!
 
Actually that does really help!!


I think I get it .. is cell(I,2) referencing the 2nd collum from i? and it's saying that the 3rd column = 2nd column * UserInput?


One last question: how does it know I is the cells b2:b7?
 
Thanks also for your kind words and encouragement, this has been on my to-do list for years, so now I have some time, here I am!!


I see what you mean about learning path now - and I've certainly got a varied one going, but so far (this week) so good! I'll keep you posted!


Again, thank you so very very much for your replies & encouragement here!
 
Hi, cash_flow_pro!


You can reference cells in many ways.

One is Range("B2:B7"), it references cells B2:B7.

Other is Range(Cells(2,2), Cells(7,2)), same result.


In this case, the For...Next cycle is executed 7 times, varying the index and control value from 1 thru 7, stepped by 1 (full syntax is "For variable = starting_value To ending_value Step increment_or_decrement). That's how Excel knows which cell to address in each cycle according to the value of I.


Regards!
 
thanks! i'm sure this is a silly question, but i still don't think we specified i as the range b2:b7, did we?


i'm still receiving the run-time error and notice that it's leaving 0's in cells c1 and c2 ..


I've also changed my excel sheet around slightly so now the range of cells is called ShoppingList (and it's now in cells b4:10 .. how would I change the code to allow for that?


sorry to have so many boring questions for you!


Thanks.
 
errrm, ok .. should I upload a version for you?


I have already solved the problem using Hui's answer .. but if you're open to discussions, that would be fan-taddy-tastic!!?


Let me know?
 
Hi, cash_flow_pro!


I thought you didn't solved it, but if you did there's no need to upload your file. Unless you want to.

But let us do the invert process. Here's my uploaded file with the code copied and pasted from this topic and working. Check it and see if it works for you. If not, upload your file.

https://dl.dropbox.com/u/60558749/VBA%20to%20multiply%20small%20list%20by%20UserEntry%20value_%20%20%28for%20cash_flow_pro%20at%20chandoo.org%29.xlsm


Regards!
 
I didn't solve it with your technique .. yet!


Thanks for uploading the example (I was still searching through the list of sites to use trying to decide which one's better! DropBox?) ..


So .. what happens if you move the list .. say by inserting two rows at the top .. then the macro still uses cells b1:c7 .. why is that?


I think I'm failing to understand how it knows the original list is in b1:b7 ..


Say I was to name the list 'ShoppingList' because it's not in a constant place .. how should I allow for that?


Thanks!
 
Hi, cash_flow_pro!


The code searches for you original specifications: reads B1:B7, stores C1:C7. Each change you make to that range in the worksheet should be reflected in the code. If you named a range ShoppingList, replace the "7" in the For...Next by "Range(ShoppingList).Rows.Count" and replace "ActiveSheet" by "Range("ShoppingList")".


Regards!
 
You must log in or register to reply here.
Back
Top