counting in letters

thanks and sorry about the wrapping, I didn't know how to do that until you replied. I will try your suggesting and see if this helps. THanks again.

Originally Posted by VBA Noob

Thanks for wrapping code.

Maybe

Please Login or Register  to view this content.

VBA Noob

How does this feature work when I am referencing other sheets? Could you give me an example of how to do this as well?

my goal is to place equations into cells that reference other sheets cell locations. i know this is wrong but something like this:

Please Login or Register  to view this content.

Plus I want the sheet# to be a variable so I can count through the sheets. I hope this makes sense.

I didn't include formulas because they are massive and lots of them. There are over 20 formulas but here is an example of one.

Please Login or Register  to view this content.

A2 is the name of the sheet that it is referencing. this is just one of many formulas as this workbook is massive. thanks for the help.

Originally Posted by VBA Noob

And the actual cell formula would be

Why are you doing it in code?

VBA Noob

I need to run these in code because I have to calculate tons of data for different inputs for all the hours in a year. So I feed 8,760 hours day by day, month by month in order to calculate the energy needed. This spreadsheets are massive.

Its easy to use the ROW number to move around inside of a FOR LOOP but I want to be able to move around the same way with the column number. I have to jump from sheet to sheet so I am wondering how you piece that together with the cell(row,column) notation.

In the event that anyone cares, this is basically what I ended up going with.

Please Login or Register  to view this content.

thanks vba noob for the reply with the CELLS(Y,X) notation which allows me to do what I wanted.

Wow, I am tired this morning. This actually still does not accomplish the task at hand. If/when I figure it out I will update my solution.

Please Login or Register  to view this content.

What I need to do is to turn the hard code for the letter "C" in the above code into a variable somehow that will be a Letter representative of where we are in the area count. So when area = 2 it will write in a B and when area = 3 it will write in a 3 and so forth that way it counts across the columns correctly. Does this make sense to anyone?

Alright, I believe I have tracked my solution down to being able to use the ADDRESS() function to solve my problem. however, when I attempt to use the function ADDRESS() in VBA I get a "Object doesn't support this property error". How do I get it so that I can use the Address function in my Macro?

Thanks

I tried using this below but it doesn't work.

Please Login or Register  to view this content.

The excel function

Please Login or Register  to view this content.

does exactly what I want. However, I want to use this function inside of a macro. I have tried both of the following lines but neither works.

Please Login or Register  to view this content.

Does anyone know what I am doing wrong here?

If you want to return a Range as a variable, you have to use the set command. There may be better ways to do this, but it is difficult to give you an answer without seeing your code and knowing what you ultimately want to do.

Originally Posted by BigBas

If you want to return a Range as a variable, you have to use the set command. There may be better ways to do this, but it is difficult to give you an answer without seeing your code and knowing what you ultimately want to do.

The Address function returns the address of the given numbers. Example: Address(2,2) returns $B$2

I simply want to do this in a macro. I want to feed a variable the address of 2 coordinates and have it store that value.

Please Login or Register  to view this content.

If the cell $B$2 contains the value 8, I would expect the value of 8 to now be in cell A2. Does that help?

Originally Posted by VBA Noob

Soce,

Threads merged as per forum rules.

As I said if you want a proper answer give a proper example

VBA Noob

Does my example above help? I simply want to use the function ADDRESS inside a macro. How do I do so? It works perfectly fine in my excel spreadsheets but does not work in the Macro.

Ok, I want to write a macro that will ultimately result in writing this equation into the cell B3.

Please Login or Register  to view this content.

Basically, I just need to be able to turn my 2-D coordinates into a cell coordinate which the function ADDRESS does perfectly. However, I need this to work inside my macro so that I can have that B3 and C2 above change for every hour of the day and every area in the study.

does this make sense? I simply want to use the function = Address(2,2) for example and put its value into a variable so I can then use the variable to form the code above for every case.

Have you tried using Cells()? In VBA, Cells(2,2) return the value of cell B2.


Try working with that.

first, thanks for taking the time to help. I think I am getting closer to correctly describing what I am trying to do but not quite there yet.

What I want to do is to use the SOLUTION to the function Address(2,2) as part of my argument to another cell. So ultimately, I want to be putting the result of address(variable, variable) into a line of macro as i count through various variables. I figured I could do this by assigning the result to a variable called Location and then using the variable Location inside the argument that writes in a formula into a cell. Does this make sense?

do you have to have the cell active to do this? lets say for sake of argument that you simply wanted to write the resultant ADDRESS of various locations. So lets say that I wanted to write a program in a macro to do the following.

Please Login or Register  to view this content.

I want the value $A$1 to be written in cell A1, value $B$1 in cell B1, so on and so forth. How would I correct the above code so it doesn't blow up?