Correct usage of Cells()

F

fredrerik84

Active Member
Joined
Feb 26, 2017
Messages
383
Hi I hope some of you that are more experienced then me can help me with the correct usage of cells()

In my code I have it like this

Cells(z, "AM")

Where Z is an counter , this works perfect , but if I want the counter on the column instead , what do I need to do

I have this solution, Cells(21, z) which works but would be better that I could use column letter instead of 70 as its hard to tell (for me where column number 70 is.

I have tried this: Cells("M", z) but this gives an error :/
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
The Cells property has two parameters: RowIndex and ColumnIndex.
RowIndex can be only numeric value.
ColumnIndex can be either number or string.
Let's take cell F10. It could be written in two ways: Cells(10, "F") or Cells(10, 6).
 
Upvote 0
It's unclear what you are trying to do. It's always best to include examples with cell references. Remember: you know what you are looking at and trying to do, but w don't. So you have to "show and tell," rather than just "tell."

I'm sure someone will be able to help you once we can see what you're working with and understand your goal clearly.
 
Upvote 0
Hi guys, thanks for quick reply :)

Let's take cell F10. It could be written in two ways: Cells(10, "F") or Cells(10, 6).
Click to expand...
Yes this is the only way I know also

It's unclear what you are trying to do
Click to expand...

Well my sheet has about 100 columns and using cells this way cell(10, 58) I need to count - or with trial and error figure out which column 58 is. There must be a better way ?


 
Last edited:
Upvote 0
I don't get what you mean "which column 58 is". You have two flexible ways of referring to cells. What doesn't satisfy you?
 
Upvote 0
Well guess i will just use it this way , just think its pain to figure out which column the column number represents.
 
Upvote 0
fredrerik84 said:
just think its pain to figure out which column the column number represents.
Click to expand...

Code: 
Sub xxx()
    Dim AlphaCol As String, z As Long
    z = 58
    Cells(21, z).Select
    AlphaCol = Split(Cells(1, z).Address, "$")(1)
    MsgBox AlphaCol
End Sub
 
Upvote 0
In Immediate window you can always see the column: ?Cells(1, 55).Address(0, 0)
 
Upvote 0
Ah nice , I didn't know this, Even better solution .. thanks :)

Edit : I was not able to find it , ? how do I get the info to be displayed there ?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
135
Akuini
Akuini
S
  • Solved
Compare cell in column to cells below
2
Replies
13
Views
584
kevin9999
K
D
  • Question
Form Control to open a window or form to add a list of numbers, perform a calculation and paste value to a selected cell.
Replies
0
Views
83
DaveCutting
D
drom
  • Question
Formula to get table address
Replies
2
Views
85
etaf
etaf

Forum statistics

Threads
1,214,515
Messages
6,119,970
Members
448,933
Latest member
Bluedbw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top