range to cell

Hello,
I am writing a program.
I have a excel range object.
And I want to point another cell at this range.
how can I do that?
On the range there is column = 4 row = 3.
but I cannot just say
=4,3
or something like that?
don't you need
=D3?
why is the column D and not 4?
strange


0
greg4709 (89)
4/3/2007 5:56:34 PM
excel 39879 articles. 2 followers. Follow

6 Replies
492 Views

Similar Articles

[PageSpeed] 32

=sheets("Sheet1").range("D4")
or
=sheets("Sheet1").cells(3, 4)
-- 
HTH...

Jim Thomlinson


"greg" wrote:

> Hello,
> I am writing a program.
> I have a excel range object.
> And I want to point another cell at this range.
> how can I do that?
> On the range there is column = 4 row = 3.
> but I cannot just say
> =4,3
> or something like that?
> don't you need
> =D3?
> why is the column D and not 4?
> strange
> 
> 
> 
0
4/3/2007 6:26:07 PM
Sorry should be D3 not D4...
-- 
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> =sheets("Sheet1").range("D4")
> or
> =sheets("Sheet1").cells(3, 4)
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "greg" wrote:
> 
> > Hello,
> > I am writing a program.
> > I have a excel range object.
> > And I want to point another cell at this range.
> > how can I do that?
> > On the range there is column = 4 row = 3.
> > but I cannot just say
> > =4,3
> > or something like that?
> > don't you need
> > =D3?
> > why is the column D and not 4?
> > strange
> > 
> > 
> > 
0
4/3/2007 6:32:06 PM
thanks jim.

however i am still a bit confused.
lets say i want to not assign the value.  but reference the cell.
so if i am in cell A1 or 11
if I do this
Excel.ActiveCell = Excel.ActiveSheet.Cells(1, 2)
it will just asign the value of the cell
and this
Excel.ActiveCell = "=" & Excel.ActiveSheet.Cells(1, 2)
will be "=<value of cell>"

is there a way to just reference the cell?

thanks






"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:4E7E88BB-C194-48B5-A780-6F491E8828AA@microsoft.com...
> Sorry should be D3 not D4...
> -- 
> HTH...
>
> Jim Thomlinson
>
>
> "Jim Thomlinson" wrote:
>
> > =sheets("Sheet1").range("D4")
> > or
> > =sheets("Sheet1").cells(3, 4)
> > -- 
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "greg" wrote:
> >
> > > Hello,
> > > I am writing a program.
> > > I have a excel range object.
> > > And I want to point another cell at this range.
> > > how can I do that?
> > > On the range there is column = 4 row = 3.
> > > but I cannot just say
> > > =4,3
> > > or something like that?
> > > don't you need
> > > =D3?
> > > why is the column D and not 4?
> > > strange
> > >
> > >
> > >


0
greg4709 (89)
4/3/2007 6:57:00 PM
So if I follow you (which I am not too sure that I do) you want one cell to 
reference the other cell such that when you change the cell the referenced 
cell is also changed??? If so you probably want to use the change event to 
catch changes to the first cell and then replicate those changes to the other 
cell.
-- 
HTH...

Jim Thomlinson


"greg" wrote:

> thanks jim.
> 
> however i am still a bit confused.
> lets say i want to not assign the value.  but reference the cell.
> so if i am in cell A1 or 11
> if I do this
> Excel.ActiveCell = Excel.ActiveSheet.Cells(1, 2)
> it will just asign the value of the cell
> and this
> Excel.ActiveCell = "=" & Excel.ActiveSheet.Cells(1, 2)
> will be "=<value of cell>"
> 
> is there a way to just reference the cell?
> 
> thanks
> 
> 
> 
> 
> 
> 
> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:4E7E88BB-C194-48B5-A780-6F491E8828AA@microsoft.com...
> > Sorry should be D3 not D4...
> > -- 
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > =sheets("Sheet1").range("D4")
> > > or
> > > =sheets("Sheet1").cells(3, 4)
> > > -- 
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "greg" wrote:
> > >
> > > > Hello,
> > > > I am writing a program.
> > > > I have a excel range object.
> > > > And I want to point another cell at this range.
> > > > how can I do that?
> > > > On the range there is column = 4 row = 3.
> > > > but I cannot just say
> > > > =4,3
> > > > or something like that?
> > > > don't you need
> > > > =D3?
> > > > why is the column D and not 4?
> > > > strange
> > > >
> > > >
> > > >
> 
> 
> 
0
4/3/2007 7:32:07 PM
thanks for the help.
Not exactly.

For example, I would Like to have cell A1 = B1
But I only have a excel range object.
So in the range object I have column 2, row 1
so in my current cell, how can i reference this?
instead of saying =B1?
because i have column 2?
is there an easy way around this?

thanks for any help



"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:97492483-0014-48BD-8F0D-0091F8061978@microsoft.com...
> So if I follow you (which I am not too sure that I do) you want one cell
to
> reference the other cell such that when you change the cell the referenced
> cell is also changed??? If so you probably want to use the change event to
> catch changes to the first cell and then replicate those changes to the
other
> cell.
> -- 
> HTH...
>
> Jim Thomlinson
>
>
> "greg" wrote:
>
> > thanks jim.
> >
> > however i am still a bit confused.
> > lets say i want to not assign the value.  but reference the cell.
> > so if i am in cell A1 or 11
> > if I do this
> > Excel.ActiveCell = Excel.ActiveSheet.Cells(1, 2)
> > it will just asign the value of the cell
> > and this
> > Excel.ActiveCell = "=" & Excel.ActiveSheet.Cells(1, 2)
> > will be "=<value of cell>"
> >
> > is there a way to just reference the cell?
> >
> > thanks
> >
> >
> >
> >
> >
> >
> > "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in
message
> > news:4E7E88BB-C194-48B5-A780-6F491E8828AA@microsoft.com...
> > > Sorry should be D3 not D4...
> > > -- 
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > =sheets("Sheet1").range("D4")
> > > > or
> > > > =sheets("Sheet1").cells(3, 4)
> > > > -- 
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "greg" wrote:
> > > >
> > > > > Hello,
> > > > > I am writing a program.
> > > > > I have a excel range object.
> > > > > And I want to point another cell at this range.
> > > > > how can I do that?
> > > > > On the range there is column = 4 row = 3.
> > > > > but I cannot just say
> > > > > =4,3
> > > > > or something like that?
> > > > > don't you need
> > > > > =D3?
> > > > > why is the column D and not 4?
> > > > > strange
> > > > >
> > > > >
> > > > >
> >
> >
> >


0
greg4709 (89)
4/3/2007 9:00:03 PM
Greg

Sub getnextCol()
For Each c In Selection
  c.Value = c.Offset(0, 1)
Next
End Sub

select the column and run - it copies the the values in the next column so 
as you say, A1 = B1

Hope this helps

Peter

"greg" wrote:

> thanks for the help.
> Not exactly.
> 
> For example, I would Like to have cell A1 = B1
> But I only have a excel range object.
> So in the range object I have column 2, row 1
> so in my current cell, how can i reference this?
> instead of saying =B1?
> because i have column 2?
> is there an easy way around this?
> 
> thanks for any help
> 
> 
> 
> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:97492483-0014-48BD-8F0D-0091F8061978@microsoft.com...
> > So if I follow you (which I am not too sure that I do) you want one cell
> to
> > reference the other cell such that when you change the cell the referenced
> > cell is also changed??? If so you probably want to use the change event to
> > catch changes to the first cell and then replicate those changes to the
> other
> > cell.
> > -- 
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "greg" wrote:
> >
> > > thanks jim.
> > >
> > > however i am still a bit confused.
> > > lets say i want to not assign the value.  but reference the cell.
> > > so if i am in cell A1 or 11
> > > if I do this
> > > Excel.ActiveCell = Excel.ActiveSheet.Cells(1, 2)
> > > it will just asign the value of the cell
> > > and this
> > > Excel.ActiveCell = "=" & Excel.ActiveSheet.Cells(1, 2)
> > > will be "=<value of cell>"
> > >
> > > is there a way to just reference the cell?
> > >
> > > thanks
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in
> message
> > > news:4E7E88BB-C194-48B5-A780-6F491E8828AA@microsoft.com...
> > > > Sorry should be D3 not D4...
> > > > -- 
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > =sheets("Sheet1").range("D4")
> > > > > or
> > > > > =sheets("Sheet1").cells(3, 4)
> > > > > -- 
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "greg" wrote:
> > > > >
> > > > > > Hello,
> > > > > > I am writing a program.
> > > > > > I have a excel range object.
> > > > > > And I want to point another cell at this range.
> > > > > > how can I do that?
> > > > > > On the range there is column = 4 row = 3.
> > > > > > but I cannot just say
> > > > > > =4,3
> > > > > > or something like that?
> > > > > > don't you need
> > > > > > =D3?
> > > > > > why is the column D and not 4?
> > > > > > strange
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >
> 
> 
> 
0
BillyLiddel (193)
4/4/2007 4:18:04 PM
Reply:

Similar Artilces:

Merging and formatting a cell in workbook_open
Hi, I have a workbook_open event, in which Im unprotecting a sheet, copy a cell's contents (A3 Cell) in that unprotected sheet and paste it in to cell J12 of the same sheet and then protecting back the sheet. Idea seeking - Im quite wary of my VB project password being "hacked" in to. Plainly speaking I dont have much problem if they see the code etc but in the workbook_open event the password of the sheet is given which I would not like to disclose. Its because I use the same password for protecting other sheets and the workbook itself. I understand that worksheet passwords ca...

How to reference another cell's content directly in a formula
Hi all, For a cell such as that in A2 below: A B 1 4Q 2005 1Q 2006 2 ='4Q 2005'!$R$7 3 The '4Q 2005' refers to a sheet in the same excel work book with that name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'. Is there any function I can use so that cell A2 uses the data in cell A1 to figure out which sheet name to look for? I'm doing this so that I can copy and paste cell A2 into cell B2 and don't have to manually input '1Q 2006'. Something to the effect of the pseudocode = 'content_of(A1)'!$R$7. When copied and p...

Show results in bold if a cell is in bold
Good morning, I have this sheet with some data and other one with results. The results are calculated with hlookup from the data. Data that changes weekly are introduced in bold. Is there any chance to show the results of the data in bold in bold as well. I mean, if the data is in bold, resulta must be in bold. If data is without format, must show without any format. I supposed we would need to use a macro. Is it possible? Thank you so much for you support Formulas don't bring back formatting. But maybe you could use a helper cell in the original worksheet that shows if th...

Save file with cell name
Hi In cell A1 i have =cell"filename"A1 giving me the I want to run macro that will save new file with data in A1 Example. tab named wc0701. new file saved as wc0701 Any idea's regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200601/1 You could just drop the formula in A1 and use the worksheet name itself. with activesheet .parent.saveas filename:="C:\" & .name & ".xls", ...rest of options end with "Brian Thompson via OfficeKB.com" wrote: > > Hi > In cell A1 i have =cell"f...

I can't see which cell I am on...
If I am working in a spreadsheet with multiple colors, as I move from cell to cell the border is highlighted a contrasting color, e.g the border on a turquise cell is red. This makes it easy to see which cell I am on. But I have another spreadsheet with no background color and black text, with many of the cells with a black border. When I am on one of those cells the border shows as black, but you really can't tell which cell you are on amongst all of the others outlined in black. Is there a way to use a more vivid contrasting color, such as red, to highlight the border of the ce...

How can I enter a cell in excel with hotkey or combination off key
F2 ?? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "theo passier" <theo passier@discussions.microsoft.com> wrote in message news:8DA5E96E-8A5B-43F2-A15B-210C14A94E0F@microsoft.com... > ...

Cell format protection
I have certain cells locked in a protected worksheet. The spreadsheet is used by others who input data in unlocked cells and return to me. My problem is that when they copy & paste something, they are eliminating the formats I have all ready set (border, allow numbers only, etc.). Is there any way to prevent this, while still maintaining the protection of the rest of the locked cells? ...

How to change cell color based on content
Hi, hope someone can help me. I don't use Excel much, but I'm creating a spreadsheet that keeps track of ticket sales for a charity I volunteer for. Anyway, what I want to do is to make the cells stand out for those that still owe money, either because a check is forthcoming or because they ordered by credit card. I want the credit card orders to show up with a different color (eg YELLOW) than those who are fully paid. I want the forthcoming check orders to show up in a different color (eg RED). So I have a column with payment type that is either Cash, Check, or Credit Card....

Non-Printing Cells
Can anyone tell me whether it is possible to format individual cells so that they do not print. I do not want to hide rows or columns as I want to be able to see the contents on the screen. Thanks Andrew, Almost what you want... Use a custom number format of 3 semicolons ;;; The content of the cell will appear in the formula bar but not on the screen. Regards, Jim Cone San Francisco, CA "Andrew Mackenzie" <a_d_mackenzie@hotmail.com> wrote in message news:OXsY9zBZEHA.556@tk2msftngp13.phx.gbl... > Can anyone tell me whether it is possible to format individual cells so...

Cell value not recognize in an expression
=SUMIF(B21:B24,">=C18",C21:C24 Any reason why the cell value of C18 would not be recognized, if I replace the value of c18 in the expression, it works but putting the value of C18 as a reference in the expression give me zero as an answer. Hi jaclh2o! Use: =SUMIF(B21:B24,">="&C18,C21:C24) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "jaclh2o" <anonymous@discussions.microsoft.com> wrote in message news:10CA0900-E305-4144-A0B4-16B092EFFD73@microsoft.com... > =SUMIF(B21:B24,">=C18",C21:C24) &g...

named ranges #2
Hello gurus, This is a fun project, or it could be with a little help for you fine folks. I am establishing named ranges to create dynamic charting. Since I will be doing 100+ charts for multiple groups at my company, I’ve set up a standard format for each set of named ranges that generate the charts. However, I am not that far along and defining a new set of named ranges (approx 12 per chart = 1,200 names) 100 times over has proven quite daunting, even with the use of the name manager (http://www.jkp-ads.com/officemarketplacenm-en.asp). Each group will have one workbook, each with...

blank cell #3
How do I usse conditional formatting to tell all the cells in a particular column to change one color if there is something in a cell, and another if it is blank? The column in question is the "Date Closed" column and I want the blank cells to stand out from the rest. Thanks Ross Select the column (I used E). And with E1 the active cell... Format|conditional formatting formula is: =E1="" choose a nice format Ross wrote: > > How do I usse conditional formatting to tell all the cells in a particular > column to change one color if there is something in ...

how can i select all the cells with same color on a sheet if there are multipale colors by vba code
how can i select all the cells with same color on a sheet if there are multipale colors by vba code Much better than the 1st post. try this Sub selectyellows() For Each cel In ActiveSheet.UsedRange If cel.Interior.ColorIndex = 6 Then mystr = mystr & cel.Address & "," End If Next cel mystr = Left(mystr, Len(mystr) - 1) Range(mystr).Select End Sub -- Don Guillett SalesAid Software donaldb@281.com "uobt" <uobt@aol.com> wrote in message news:1103129633.880542.286200@f14g2000cwb.googlegroups.com... > how can i select all the cells with same color on a shee...

can i link a cell to a calendar so it will auto update
i am trying to get a cell to change font color when it passes a certain date Format > Conditional Formatting, Cell value is greater than [date] Select Format and change text color to desired color "paulyoung666" wrote: > i am trying to get a cell to change font color when it passes a certain date ...

Lastrow in Range
Hello, This is the VBA macro that I use: Set rng = Range("h2:h167") For Each cell In rng If IsNumeric(cell.Value) Then cell.Value = Application.WorksheetFunction. _ RoundDown(cell.Value, 2) End If Next I'd like to set the rng to automatically start from H2 to the last row in column H. Thanks, try Set rng = Range("h2:h" & cells(rows.count,"H").end(xlup).row) -- Don Guillett SalesAid Software donaldb@281.com "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:8C10211C-8D30-49A0-80E8-ABCE7CE0F7CB@...

Format cell if cell is zero
I have Excel that show text after Calculating in the cell. I want the cell dosen't show the text before the cell is typing any value. How I do that ?? // sokoban ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If the value is typed in cell B3, and the text is in C3, enter the following formula in cell C3: =IF(B3=0,"","This is the text") or =IF(B3="","","This is the text") When you type in B3, the text wil...

Confine results of web query to one cell
How do you confine the results of a web query to one cell in Excel 2004? Under Excel 2004 a web query always seems to use up two cells in a given column no matter what data range properties I set. I never had a problem with this in Excel 2000. ...

Celling formatting failing
Hi All, I am having an unsual behaviour and am trying to track the cause. In excel 2000 I am entering the date 17/12/1969 in a cell e.g. B2 then a new date 17/12/2004 below it in C2 then lastly third a date 17/12/2005 in D2. I then highlight the 3 cells which show the date exactly as entered above. I go to Format > Cells and change on the number tab the category from General to date. I then select the desired type (in my case being 17-Dec-69 in the first dates case). The behaviour I see is that the Sample preview above the types field does NOT change to match the selection. Nor does it ...

can I sort colored cells?
Hi, Can I sort a column that some cells are colored "red" and and some "yellow" in the nearby columns seperately via VBA?. Please provide a sample code that does the job. Thank you for your time. JACK Yes ! with this code (paste in a module VBA) Function MyColor(CkCell As Object) Application.Volatile True MyColor = Abs(CkCell.Interior.ColorIndex) ' not for the conditionnal color !!! End Function In a diff�rent colum, you type = mycolor(A1) and you sort by this nex column Bye -- ....Patrick Quoi que vous fassiez, faites le bien . Mail: http://cerbermail.com/...

cell formatting problems
I installed Office 2000 onto my desktop before I reformatted the hard drive and everything worked fine. I reformatted and then got a laptop. For some reason certain functions are now refusing to work. Funtions such as: formatting individual cells and colouring individual cells (or lines). I can't change any of the cells properties...the window doesn't come up at all when I do Ctrl+1. Also, when I try to colourize some cells...nothing happens. Please, please help me. I have uninstalled and then reinstalled with no resolution to the problem. I've tried installing different fea...

Linking a Message Box to a cell value
Okay, this is probably the worst way to do things, but I want to link cell value to a message box. I am basically going to use the messag box as a method of data validation. If the data value of the cel exceeds a certain data range, then the message box should pop up an alert the user that his input data is "out-of-range" I can't really use the data validation approach. I have a drop dow menu next to the input data cell to allow for different input units. This drop down menu is used along with conversion factors to a specifi unit that I want to use in my calculations. The ...

3 format cell tabs hidden in excel dialog box
When I right click on the cell and go to format cells instead of seeing 6 tabs as usual I see three tabs and the other three are on a lower level and covered by the information in the dialog box. I can only see the very top edge of the tabs. Any ideas what could be causing this? If you're using Excel 97, and making copies of copies of worksheets, you may hit a limit, because of the way the sheets are being numbered in the background. If that's the case, try copying one of the first sheets, instead of the last one. pjbjamison wrote: > When I right click on the cell and g...

How do I restore format cells menu?
I am working in Excel 2002, multi-page workbook. On some pages "format cell" is not operational - neither from the toolbar or by right-clicking mouse. Command is there but produces no result. On other pages in the same book all is well. There are no protected pages, and this is happening only in one particular workbook. Have tried reset to no avail. When copy and paste material to properly functioning page problem is carried as well. Any ideas would be muchly appreciated. ...

Chart Axes Linked to cell values
Hello Everyone. I have some sort of a problem with linking chart axe (min and max values) to cell values in a worksheet. I am using th macro that I found in Mr Peltier' site: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html It worked just fine for a simple test I was doing.. BUT, I came up wit the idea of assigning to the cells (the cells that contain the scal params) some worksheet functions, to determine the values from a set o data. When I did this, the macro didn't work anymore. It didn't pop u any errors or such things, it just didn't adjust the chart anymo...

Chart with Named Range error
Using Excel 2007, creating a chart using named ranges as inputs when I try ti enter the named range as a source I get a formula error "the formula you typed containes an error..." So I setup a simple scenario to dumlicate the problem. named a range of cells "Test" and entered arbitrary numbers in the cells of the range. Insert chart, Right click on the chart and select "select data" then "add" In the box type "=Test" or used the "Use in formula/paste name" tool. Select ok and get the formula error. I dont think I had any problems w...