View Full Version : Get values in sheet2 from sheet1 data

02-01-2006, 04:58 PM
http://vbaexpress.com/forum/images/smilies/sad2.gif I am having problems... stuck... I don't know what to do next...I don't know how to command... I just hoped it would work but.. no...please help me...
I am trying to calculate two ways of traveling distance flying and driving. I am subtracting distancest that is not acquire for an actual distance. If I traveled twice, by air and by car. I want to have a table (like on sheet2). Can anyone please help me or fix my macro that doesn't make sense but only to me?

02-01-2006, 07:22 PM
Hello fanoftu http://vbaexpress.com/forum/images/smilies/039.gif Welcome,

I see that you edited your post to try to include a file...but I don't see a file http://vbaexpress.com/forum/images/smilies/090.gif could you try again, and I'll be more than happy to help out http://vbaexpress.com/forum/images/smilies/023.gif


02-01-2006, 08:47 PM
This is what I want my macro to work so i can use this for my further travel distances...I am hoping someone can rescue me...:hi: I have ..tried... different ways.. and I don't know if I am even doing the right thing to excute this.. marco...


02-01-2006, 09:50 PM
Cross post http://www.ozgrid.com/forum/showthread.php?t=45887

02-01-2006, 10:01 PM
oh.. i thought this was a different one.... well as u can see.. i am clinging for a help.. am i still not making sense at all?... I should just quit.. egh?:(

02-01-2006, 10:02 PM
:banghead::banghead::banghead::banghead::banghead::banghead::banghead::bang head::banghead:

Sorry fanoftu, but I was almost finished with your file and Excel crashed on me :igiveup:

Man that's aggravating (as I'm sure you can tell)....and I was so close :mkay

If no one solves this by tomorrow...I'll give another crack at it :thumb

Even though I lost the info...here's what you can look into:

1. Userform_Initialize - use this to set the listbox items. I noticed if you ran the macro while in Sheet2, the listbox wouldn't fill correctly.
2. Unload Me / Userform1.Hide - this you should use to hide the userform(1 or 2) when you hit the OK button.
3. Public Variables - place one of these in the Module1 code you have, name it monthSelected and declare it as a string. Once the OK button is selected from UserForm1 --> monthSelected = ListBox1.Text ...now you can use this for the other userform to find the month in Sheet1 and use Offset to get the corresponding value depending on which option you choose.
4. Double check Syntax - I noticed your syntax is incorrect here (along with the option2's syntax)...
Worksheets("sheet2").Value = x
z = x - Range("b")
You can't set a worksheet to a value...at least not that I know of :think:. You need to specify a Range. And you are not selecting a correct Range with "Range("b")", there needs to be a number after "b". EDIT: You should also specify a Sheet.

...Just to get you started :thumb

02-01-2006, 10:22 PM
I will try to do what you are saying... If i understood VB better.. I would know what you are saying..but I am still learning little by little and this is so frustrating... and I am so sorry that it crashed...why that doesn't happen to me so i have an excuse???:motz2: <<< Me So.. If i can't set the worksheet put a value... how am i getting a value? on each row or column.??? omg.. I feel flying stupid about now... (:banghead: ) sighs.. Thank you so much for your help now, I guess it might seem to work little further than what i have now...:rotlaugh: )


02-01-2006, 10:36 PM
Nah don't feel stupid...it's all about learning :)

You need to state a specific range to collect a cell's value:
Msgbox Sheets("Sheet1").Range("A1")
This tells you cell A1's value on sheet1.

Check this place out: http://www.geocities.com/WallStreet/9245/

Maybe it will help you out :thumb

02-01-2006, 10:59 PM
:doh: where do we put? Userform_Initialize , inside of ok box in userform1 or inside of listbox1 or.. somwhere else? or make new one? or ?... should i just stay quiet? for now?... sighs... :help

02-01-2006, 11:05 PM
Userform_Initialize goes within the Form's code, but by itself. Right-click your Userform1 and select "View Code" and...
Private Sub Userform_Initialize()
'Initial Items such as the list box
End Sub
That's all for tonight...I'm going to sleep now :cloud9: aaahhh....bed :)

I'll look at this tomorrow night :thumb And if you get an answer before then on the other site, please let us know :yes

02-03-2006, 11:15 AM
I still getting error messages... I think I am out of my knowledges to fix the problem... sighs...any helpers? :help