use variable value in a range reference?

My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

 With Range("formulaCells")     'named range
        totalRows = .Rows.Count
        lastRow = .Rows(totalRows).Row
    End With

myformulaTotalRows = totalRows
    mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like 
Range("C6").select where the row number 6 can be  replaced by  
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown statement

Thanks for any help
0
Utf
3/26/2010 9:54:06 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
657 Views

Similar Articles

[PageSpeed] 56

Range("C" & mylastformularow).select
or
cells(mylastformularow, "C").select



JCIrish wrote:
> 
> My code is
> 
> Dim myformulaTotalRows As Integer
> Dim mylastFormulaRow As Integer
> 
>  With Range("formulaCells")     'named range
>         totalRows = .Rows.Count
>         lastRow = .Rows(totalRows).Row
>     End With
> 
> myformulaTotalRows = totalRows
>     mylastFormulaRow = lastRow
> 
> Is there a way to use "mylastFormulaRow" in a statement like
> Range("C6").select where the row number 6 can be  replaced by
> "mylastFormulaRow"?
> 
> I want to use the selected cell as the first argument in a .filldown statement
> 
> Thanks for any help

-- 

Dave Peterson
0
Dave
3/26/2010 10:11:26 PM
Sub fillitdown()
'Range("e1").AutoFill Range("e1:e4")
dim lr as long
lr = Cells(Rows.Count, "e").End(xlUp).Row
Cells(lr, "e").AutoFill Cells(lr, "e").Resize(4)
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"JCIrish" <JCIrish@discussions.microsoft.com> wrote in message 
news:3C85C31D-30AB-4BFD-ADA7-00F75B2DFD0E@microsoft.com...
> My code is
>
> Dim myformulaTotalRows As Integer
> Dim mylastFormulaRow As Integer
>
> With Range("formulaCells")     'named range
>        totalRows = .Rows.Count
>        lastRow = .Rows(totalRows).Row
>    End With
>
> myformulaTotalRows = totalRows
>    mylastFormulaRow = lastRow
>
> Is there a way to use "mylastFormulaRow" in a statement like
> Range("C6").select where the row number 6 can be  replaced by
> "mylastFormulaRow"?
>
> I want to use the selected cell as the first argument in a .filldown 
> statement
>
> Thanks for any help 

0
Don
3/26/2010 10:13:59 PM
Thanks, Dave. I must have tried ten things similar to your solution except 
including the &. I appreciate the help.

"Dave Peterson" wrote:

> Range("C" & mylastformularow).select
> or
> cells(mylastformularow, "C").select
> 
> 
> 
> JCIrish wrote:
> > 
> > My code is
> > 
> > Dim myformulaTotalRows As Integer
> > Dim mylastFormulaRow As Integer
> > 
> >  With Range("formulaCells")     'named range
> >         totalRows = .Rows.Count
> >         lastRow = .Rows(totalRows).Row
> >     End With
> > 
> > myformulaTotalRows = totalRows
> >     mylastFormulaRow = lastRow
> > 
> > Is there a way to use "mylastFormulaRow" in a statement like
> > Range("C6").select where the row number 6 can be  replaced by
> > "mylastFormulaRow"?
> > 
> > I want to use the selected cell as the first argument in a .filldown statement
> > 
> > Thanks for any help
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/27/2010 4:27:01 AM
Thank you,Don. That's a more elegant way to go than my Sub. I'm sure it will 
be useful to me in many similar situations.

"Don Guillett" wrote:

> Sub fillitdown()
> 'Range("e1").AutoFill Range("e1:e4")
> dim lr as long
> lr = Cells(Rows.Count, "e").End(xlUp).Row
> Cells(lr, "e").AutoFill Cells(lr, "e").Resize(4)
> End Sub
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message 
> news:3C85C31D-30AB-4BFD-ADA7-00F75B2DFD0E@microsoft.com...
> > My code is
> >
> > Dim myformulaTotalRows As Integer
> > Dim mylastFormulaRow As Integer
> >
> > With Range("formulaCells")     'named range
> >        totalRows = .Rows.Count
> >        lastRow = .Rows(totalRows).Row
> >    End With
> >
> > myformulaTotalRows = totalRows
> >    mylastFormulaRow = lastRow
> >
> > Is there a way to use "mylastFormulaRow" in a statement like
> > Range("C6").select where the row number 6 can be  replaced by
> > "mylastFormulaRow"?
> >
> > I want to use the selected cell as the first argument in a .filldown 
> > statement
> >
> > Thanks for any help 
> 
> .
> 
0
Utf
3/27/2010 4:32:01 AM
Reply:

Similar Artilces:

use of xsd.exe
VS2005. I'm running a stored procedure and am stuffing the output into a dataset. I want the dataset to be strongly-typed, but can't seem to get quite what I want out. Performing a "Fill" on a generic dataset, I can get the schema of the data being returned. A snippet appears below: <xs:element name="Table2"> <xs:complexType> <xs:sequence> <xs:element name="PRODUCT_GROUP" msprop:OraDbType="126" type="xs:string" minOccurs="0" /> <xs:element ...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

Category color changes when changing values.
When I copy a chart I get two identical ones. When I change the values of one of the charts and sort the values, Excel changes the colors of the categories to a preset order, so that the color of the biggest customer in chart 1 is not the same as this same customer (let's say now on the third place) in chart 2. Is there a way to prevent this? ...

How make range of Hyperlinks?
I see how to make a single cell into a Hyperlink but what if we want to have all email and web addresses in a spreadsheet turn into hyperlinks? Is there an easy way to turn this on and off? Hi for making hyperlinks in your selected range have a look at the following macro: Sub MakeHyperlinks() Dim cell As Range For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) With Worksheets(1) .Hyperlinks.Add Anchor:=cell, _ Address:=cell.Value, _ ScreenTip:=cell.Value, _ TextToDisplay:=cell.Value End Wit...

Invalid References in formula
Hi, I got this error message when i close my workbook: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference." The funny thing is that this error message pop out only when i save and close the workbook on certain worksheets. E.g. I have worksheet a, b, c and d. When i am either on sheet a and d, i saved the file and close the book at that sheet, the error message did not pop out. However when I performed similar actions on either of the other 2 sheets, the error m...

Saving Excel workbook in SQL server using c#
Could anyone please help me out as to how we can save the excel workbook in the database and read it back. I was able to convert the text files and image files into binary format and save them to the DB and finally able to retrive them back in the same format. But was unable to do same for the excel object. Your help will be greatly appreciated. Thanks, regards, jitender ...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

Newbie: can't get a calculated value on the form?
I have a table with numbers and a form that shows the numbers. I have a query that takes one of the numbers and mulitplies it. I put a text box on the form from the query result field, but I get a "#Name" error instead of the result. When I run the query, I get the correct result. Help, please? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:d1e7d27c-11d9-4696-8d19-4c5fdd9dbb89@d70g2000hsb.googlegroups.com... >I have a table with numbers and a form that shows the numbers. I have > a query that takes one of the numbers and mulitplies it. I p...

Looking for someone who can integrate CRM with GP using SCRIBE Integration tool
Hi, Is anyone out there in Sydney area who would like to help us in doing integration between CRM 4 and GP 10 through SCRIBE integration tool for a fee? We are looking for someone who has already done integration between these two applications using SCRIBE. You will have to do bit of customisations in the SCRIBE standard templates. If anyone available, you can contact me through my email badri1203@gmail.com Badri ...

Create a new instance of a class that is a reference
Hi all, I am trying to make a class that when you create a 2nd instance the second instance will be a reference to the first. Public Class ClassA Private Shared tableList As Hashtable Public myInt as integer = 0 Public Sub New() MyBase.New() InitializeValues() End Sub Private Function openTable() As Boolean If (tableList Is Nothing) Then tableList = New Hashtable() End If If (tableList.ContainsKey(TABLE)) Then SetReference(Me, tableList(TABLE)) OpenCount += 1 Else ...

Converting Values to Unicode Characters
The function CHAR converts a value in the range 1-255 to to an ANSI character. Is there a way to convert values in the range 1-65,342 to Unicode characters? -- Gary L. Smith gls432@yahoo.com Columbus, Ohio You can use VLOOKUP but you'll have to create your own lookup table of the unicode characters and I think you'll have to paste it onto the same spreadsheet.... Maybe you can find a lookup table on the internet somewhere that you can easily paste into your spreadsheet. tsides <tsides@intelligentsystemsconsulting.com> wrote: > You can use VLOOKUP but y...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Using A CTabView
Hi, I need to implement a tab control which contains a number of custom controls on each tab page. For example my custom control has a number of LED type static controls and a list control. Its quite small, 200x200. I need a number of them in a tab page. They need to aligned underneath each other with a static label above each giving a description of each one. I also need to be able to scroll down if I have too many to fit into one page. Has anyone done something similar or can suggest where to look?? Cheers ...

HOW USE 2 LANGUAGES IN AX. ???????????
Hello I have problem and I need Help: 1. Axapta using English Language in all system 2. I need write something in Russian Language. When I use Russian Keyboard and I wriet somethings in Axapta fields is ok, but after this Russian words was change and I see only symbol likely this ??????? (query) 3. In MS SQL database in the fields I see words likely this symbol ??????? (query) Please send my all information how I can write something in different Languages in this same Axapta - I need write simultaneously in 2 Languages. Please send answer on ma email: search1234@wp.pl Thank...

Public variable
I am calling a public sub from a form, and I am trying to get the sub to be able to access a variable from the form. I declared the variable as public in the forms code, but it isn't available to the subroutine... is this normal? Wavequation wrote: >I am calling a public sub from a form, and I am trying to get the sub to be >able to access a variable from the form. I declared the variable as public >in the forms code, but it isn't available to the subroutine... is this normal? Public variables in a form/report/class module are a property of the form/report...

Keeping a range constant when inserting rows
Hello, I'm trying to keep a range of cells constant within a function when I insert a row (e.g. average(a1:a6) becomes average(a1:a7) but I want it to keep the a1:a6 range). Even if I use absolute cell references ($a$1:$a$6), it doesn't help. I would greatly appreciate any ideas. Thanks, Jeff Jeff, In your formula, use: =AVERAGE(INDIRECT("A1:A6")) Absolute cell references (dollar signs) do one thing only: They keep any copies you make of the cell references from changing relatively as they're copied. They still change when the cells to which they refer are m...

Using a Space in Webbrowser Control
Hello: As usual, I've run into a problem with something that is probably so simple... any help is appreciated. I'm using the webbrowser control with the navigate method. The url I'm using needs a space (which I know is not normally allowed, but it works directly in firefox and IE). Here's some sample code: ------------------------------------ strWebPage = "http://www.imdb.com" &" ?" & Text2.Text txtURL.Text = strWebPage Web1.Navigate txtURL.Text Do While Web1.ReadyState <> READYSTATE_COMPLETE DoEvents Loop DoEven...

Cash-Basis Reporting using GP Analytical Accounting
We are a new GP 8 customer, still in the process of implementation, migrating from QuickBooks Pro. We are a non-profit and have unique reporting needs for our board and donors. We switched from cash-basis to accrual accounting about 2 years ago. However, a lot of our reports are still run on a cash-basis level, we need to be able to report on a cash basis as well as accrual basis. I know ther eis a cash-basis tool add-on for GP from AIM technologies. But I was wondering if the GP Analytical Accouning or multidimensional module would give us the same functionality and flexibility in g...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How to recall the Phone number ... what about other variables?
Hi just a simple question, I'm customizing my status.htm page. Without having any guide, I'm guessing almost every variable. One of the easy of them: Customer's PhoneNumber.... I tried with QSRules.Transaction.Customer.PhoneNumber but didn't bring my anything What is the right syntax? Does anybody have any list of variables you can call using QSRules ? Thanks Gustavo Gustavo, try this out: QSRules.Transaction.Customer.HomeAddress.PhoneNumber ...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

How do I determine whether my exchange server is being used to relay SPAM
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C41E7A.F70E2800 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have recently changed from Exchange 5.5 to 2003. We have also = instituted an signature program for our outgoing email. This week I have = been getting NDR's from sites and some say that our site is sending them = SPAM. I beleive that either these sites are registering the signatures = as SPAM or our server is relaying SPAM messages. How do I determine = this, I imagine that some logs would show t...

storing value in form field
Hello, I want to get values from a pop-up date form to insert in a text box on another form. Problem is when I close the date form, the value that the text box was referring to is gone. Is there a quick way to save the value in the main form until new dates are typed in? Thanks You should be able to hide the form rather than close it. "ryan" <ryan@discussions.microsoft.com> wrote in message news:49EA576C-544E-4A18-B4FB-4F7AF6D2951E@microsoft.com... > Hello, > > I want to get values from a pop-up date form to insert in a text box on > another form. Problem...