Basic Variable Question

**cosmicwinds** · 06-28-2013, 01:49 PM

Can someone give me a basic example of using variables that can be used on a different sheet but still reference their values on the sheet where all my data is.

Please Login or Register  to view this content.

The above code posts the data on sheet1 in the cell I want however it is only posting the contense of column C which I can see why. I want to have it call it as a variable not the actual spot c. I dont need my whole code written by any means, I know you all have lives to live and wouldnt expect that from someone. Just looking for a point in the right direction. Any tips or answers would be greatly appreciated. Thanks

Side note: I did post a similar thread however I felt it was a bit vague and didnt really get to the heart of what I was stuck on.

**AndyLitch** · 06-28-2013, 01:56 PM

I'm assuming that the C in Cells(1,C) is your variable ...
If you want it to work across different sheets you need to declare it in a code module.

**cosmicwinds** · 06-28-2013, 02:07 PM

You are correct, C is my variable. Sorry I didn't post more of the code I had written. I have declared it in the code.
Sub try()
Dim c
'Dim avgCallFails
'Finding Best MobileReceivePower
'*********************************************************************************************

Please Login or Register  to view this content.

**cosmicwinds** · 06-28-2013, 02:16 PM

I also tried to work back a little since this wasnt working and tried a simpiler but still in essence the same thing and couldnt get it to work.

Please Login or Register  to view this content.

**Norie** · 06-28-2013, 02:18 PM

I don't think that's all the code but perhaps something like this, assuming Cells(1, c) is on 'Series Formatted Data'.

Please Login or Register  to view this content.

PS When you post code can you enclose it in code tags.

**AndyLitch** · 06-28-2013, 02:19 PM

Looks to me that the problem is with ""= Average(‘Series Formatted Data’!c:c)""... .. You are testing for varying conditions on the source sheet but then applying the same formula to the destination --- Result being that they will all calculate the same answer.

Try Sheets("Sheet1").Cells(2, 11).Formula = "= average(Range(Cells(2, C), Cells(10000, C)))"

**cosmicwinds** · 06-28-2013, 02:19 PM

Sorry I made the corrections to code tags.

**cosmicwinds** · 06-28-2013, 02:23 PM

You are right the code will go on further looking for various other strings and output the averages and other formulas based on what it finds (at least thats the goal

). I test parts here and there to make sure they are working but this I cant really get moving since I was stuck in a rut with variable related problems.

Ok If I am understanding this correct what you said was rng was equal to cell (1,c) on X sheet.
we then loop until an empty slide occurs and within this loop we utilize a case statement to find the necessary strings. Damn thats smart >.< Case statements didnt even cross my mind

Awesome I think this should be enough to get me moving in the correct direction. I am very greatful for your insight and help as well as everyone elses

**cosmicwinds** · 06-28-2013, 02:25 PM

Alright Ill try that out as well in addition to the code supplied by Norie. Thanks for your time

I appreciate it more than you believe

**cosmicwinds** · 06-28-2013, 02:43 PM

Sorry to bother you but Im trying to get that code working and it keeps throwing an error and for the life of me I cant see where it is.

It is telling me there is a syntax error but its identical to the first case statement pretty much and that one has no issues.

Please Login or Register  to view this content.

**cosmicwinds** · 06-28-2013, 02:45 PM

Andy, so I dont actually have to specify what page it is to be implemented on, only the source location had to be specified?

**Norie** · 06-28-2013, 02:48 PM

The quotes are wrong, they should be " not ”.

I corrected some of them but missed some too.

**cosmicwinds** · 06-28-2013, 02:52 PM

Oh! I had no idea that they were interpretted differently

Learning so much since I came to this forum! Thanks again!

**cosmicwinds** · 06-28-2013, 03:49 PM

Still having the same problem it turns out. While I do agree your method looks far cleaner than mine did. From that page it is still only pulling data from the column C, not c as a variable. Sorry to keep pestering you Norie

Please Login or Register  to view this content.

**Solus Rankin** · 06-28-2013, 04:34 PM

Is c supposed to be shown as an absolute range? Why, if it is declared as a variable can it not be coded as just c?

Sorry. I'm new.

**cosmicwinds** · 06-28-2013, 04:39 PM

I am also fairly new can you explain what you mean? I am also new

**Solus Rankin** · 06-28-2013, 04:49 PM

I'm trying to view this on my phone with the excel forums app. Perhaps I misread. I thought you were trying to use the variable in your average formula because the c was lower case.

**cosmicwinds** · 06-28-2013, 04:51 PM

Yes I am trying to use c as my variable which will represent my column in a different sheet.

**Norie** · 06-28-2013, 04:56 PM

Are you saying that in this piece of code c is a variable rather than column C?

Please Login or Register  to view this content.

If that's the case I think we need more information, for example what should c change to?

Perhaps an example workbook might help?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Solus Rankin** · 06-28-2013, 04:59 PM

That's what I'm wondering. I don't see where c ever changes so it would just be 1:1 or all of column a (or c if its read as a range and not a variable).

**cosmicwinds** · 06-28-2013, 05:01 PM

oops I had forgotten to include c=c+1
However even with that added to the code it still has the same result.

**cosmicwinds** · 06-28-2013, 05:08 PM

Does this work?
Img1.jpg img2.JPG

**cosmicwinds** · 06-28-2013, 05:18 PM

c is supposed to represent my column number and if the code didnt see the string it would continue on to the next column until it found matches in various strings or it hit an empty column.

**cosmicwinds** · 06-28-2013, 07:55 PM

Well after spending all day I believe I finally figured it out. Ill update later tonight if I didnt fall asleep from being so tired.... 4 hrs of sleep a day for a couple of weeks really starts to ware on a guy. To be honest I think it was far simpler than I was making it out to be. But ya... if I fall asleep Ill update this thread first thing in the morning. Thanks again guys

**Norie** · 06-29-2013, 12:49 PM

Still not clear what you want but if c is meant to represent the column that is to be averaged I would write it something like this.

Please Login or Register  to view this content.

PS Images don't really help.

**john456852** · 06-29-2013, 01:12 PM

c has to be a static, not dim

so:

Please Login or Register  to view this content.

**Norie** · 06-29-2013, 01:15 PM

John456852

I'm not even sure c is needed.

**john456852** · 06-29-2013, 01:17 PM

ok norie. if needed, since c is used more than once, use static to keep value

**Norie** · 06-29-2013, 01:18 PM

Why would you want to keep the value?

**john456852** · 06-29-2013, 01:24 PM

because c=c+1

and after every time the sub ends, c will be reset to 0

**Norie** · 06-29-2013, 01:26 PM

That's because you want to move across the columns starting at column 1 (A).

If you declare as Static then the next time you run the code you'll start at the last column that was checked, not column 1.

So you would be skipping all the columns to the left.

**john456852** · 06-29-2013, 01:33 PM

OH sorry. i am not very good and i am not sure i understood what the original code was for.

**cosmicwinds** · 06-29-2013, 02:11 PM

Norie I needed C and I got it working. Think of it as a counter that will continue moving along checking for strings each time the counter incremented.

**john456852** · 06-29-2013, 05:48 PM

so for the counter you could use static

**Norie** · 06-29-2013, 06:00 PM

I don't see why you would want/need to use Static unless you wanted to start from the column you finished in on the previous run.

**john456852** · 06-29-2013, 06:13 PM

I guess ur righty

Basic Variable Question

LinkBack

Thread Tools

Rate This Thread

Display

Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Re: Basic Variable Question

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions