Visual Basic :: Cell Range Naming In Macro

Naming An Excel Range
Hi

I have an Excel macro and that selects certain rows (based on a text string) and then copies that row to another worksheet. This is working fine.

However, after all the necessary rows have been copied and pasted to the other sheet, I want to assign a range name to all of the copied rows. I am using the CurrentRegion.Select syntax to select all of the data, but I do not know how in VBA to give this range a name.

Any help would be great.

Thanks

Naming Cell After Sheet
Hello,
What I'am working on is a macro that will copy data to a result sheet. I dop this from different sheets in de workbook. I've allready, thanks to here, fixed that. What I want do now is give the cell that is in the collumn in front the copied data the name of the sheet. Like so:

Sheet name | Data| data| data
Other sheet | Data| data| data
etc...

Does anyone know how to do this?

Automated Macro Naming
I am after trying to find out how ( obviously cant use macro recorder to see what it records as this is too early ! Kinda like chicken and Egg syndrome I guess !!!) how to automate macro names to something like below:

Page_2
Page_4

i.e.code as such: Page_ & sheets("Avail").Range("E2")

So a button would skip the Tools > Macro > record new macro menu and automatically name the macro for you.

then a button to stop the macro.

If poss and this is poss asking too much but if its possble to do the above from 1 command button.

The reason I dont want to have to use the menus is that I don't actually want my users of the project realising they are recording a macro as such ! The macro is only recording where pictures are dragged to on the sheet for the sheet to automatically place them the next time the sheet loads that specific page...

Thanks in advance and sure hope someone can help me !

Naming Excel Worksheets With A Value From A Cell
Hi,

I am relatively new to Excel and am trying to write a macro in which a new worksheet is created using a value from a cell on another worksheet as part of its name.

I need the user to be able to enter a specific date in a box and then hit a button that will create two worksheets named "prod<value from cell>" and "dr<value from cell>" and have no idea how to go about this. I have searched and searched but am finding it quite difficult to phrase my question in a search engine friendly way.

Thanks

Naming Ranges With Excel Macro
Hi folks having problem naming a range within excel Macro.

I want to select the same area but different absolute addr's.

I use If then macro and then use offset function to select my range.

The dificulty I am having is using the right code for actually naming the range. Do I have to give absolute addresses when refering to selection?

I also have forgotten how to delete the name of a range that I have previously named.

Copying Dynamic Range Based On Cell Outside Of Range
i am copying a dynamic range of cells from twelve different worksheets. i need to select a range that starts of g10 on every sheet, but ends differently on every sheet. the range ends depending on column A in the last row of each range, the word 'total'. i would like to select the range in column g10 down to the cell corresponding with 'total' in column a in column g. For example, if there is 'total' is in a56, i want to select and copy g10-g56. If on another sheet 'total' is in a68, i want to select and copy g10-g68. there is information below the range. i want to ignore the data below altogther. here is the code that i have now for the first sheet. it grabs G10 through the last cell in column g. any help would be appreciated. thanks

Code:
Sheets("January 2005").Select
Range(Range("G10"), Cells(Rows.Count, Columns("G").Column).End(xlUp)) _
Copy Worksheets("SALES REPORT").Range("a2")

Determining Wether Current Cell/range Within A Specified Range
Hi,
I am trying to write a macro for excel spreadsheet.
I am trying to find some function or method to determine wether a particular cell is currently within the specified range or not.

A simple example to illustrate question:

Dim Action_Range as Range
Set Action_Range = Range("A1:H:10")

Dim Current_Range as Range
Set Current_Range = ActiveCell

If Current_Range is part of the Action Range then
'do something
Else
'ignore
End if

Can somebody give some suggestions?
Thanks

Automatically Calculate Row Or Cell Range Triggered By Cell Entry
Due to the slow speed of calculation (due to its size) of my excel spreadsheet I have turned calculation to manual.

I would like to trigger automatically a row to recalculate (or perhaps part of the row) once a cell entry has changed.

Can anyone help.

Excel Macro, Subscript Out Of Range, But Its In Range?
i have a 2d array:
wds(1 to 24, 0 to 1)
the values in the array are set up correctly, but when i try to
output the values of the array to a cell i get the subscript out of
range error.

Below is the code that ive used to write to the cells, to test
that the array has the correct values the commented out
msgbox was used. (it returned the correct values)
except when i use the same code to assign the value to a cell's value i getthe error.

Code:

dim count1 as integer
count1 = 4
For counter2 = 1 To 24
'MsgBox (wds(counter2, 0))
Sheets("Doubles Results").Range("C" & count1) = wds(counter2, 0)
Sheets("Doubles Results").Range("E" & count1) = wds(counter2, 1)
count1 = count1 + 1
Next counter2

thanks for any replies

ps.
with other languages ive used to do simple calculations like
variable += 3 to add 3 to the variable
variable ++ to increment the variable
is there something similar to this in VB?

Select And Copy Just Cell Content (not The Cell Itself) By VB Macro
Hi,
my problem is that I can select the content of Cell in Excel sheet [by doubleclicking (or F2) onto the Cell and simply select the whole text inside] and copy it as text.
But I cannot do this by macro.

Is there any chance to select content and copy it by VB macro?

Cell Value Based On Cell Range
I am trying to have the value of cell A multiplied by a certain amount in Cell B, based on a value range of cell C. Any help would be appreciated.

Ex:

Cell A = 25
Cell B = the value of Cell A multiplied by 10(if the value of Cell C is less than 18), multiplied by 15(if the value of Cell C is greater than or equal to 18), or multiplied by 20(if the value of Cell C is is greater than 20)
Cell C = percentage value (IE. 20.00%)

Just Another Qn Cell-range
hi
in the moment im working in my office and stupidly the admin forgot to install the object library of vba
so first qn can i get it somewhere in the i-net

and second qn how acn i save the range of a cell
lets say:
Dim i As Integer
i = activecell.range

dont laugh i know that it doesnt work

Variable Cell Range
I would like to select a variable cell range out of a previously defined range.

For example, I have a Range variable, myRange, set to the range "C3:F7":

Dim myRange as Range
Set myRange = Range("C3:F7")

I know that the syntax for selecting a single cell is as follows:

myRange(1,1).Select

However, I would like to select multiple cells within this range. For example, I would like to select all cells with top left corner (1,1) and bottom right corner (2,2).

Does anybody have any insight into this?

How To Point To A Range Or Cell?
I want to create a button that will take me to a certain cell in a given sheet.

Can it be done in the same procedure. I mean can I put the instruction in my button script to say:

''Open that sheet and go to that cell''

Reason is that the spreadsheet is very long and I want to take the viewer to a certain point in that sheet from another sheet.

Thanks!

Nex Empty Cell In Range
If I have the range A1:A65536, how do I search that range and activate the next empty cell below within the range ?

Selection Of Range From A Cell
Hi all,
I really need your help as I'm an absolute beginner in VB.
I have a cell (lets say K1) in Excel containing a range of cells i.e. "A1:F6" (this changes due to some conditions each time I open the workbook). I would like to write a short macro, selecting a range currently displayed in cell K1.
The macro is then to copy the contents of the selected range and paste them into another worksheet, in the first free (blank) row of column A.
Any ideas?

Deselcting A Cell From A Range
Hi guys, is there a command that deselects a cell from a range, I guess kinda like the opposite of intersect?

Cell Range Variables
I'm wanting to set a cell address to a variable and then increment it through a loop.

I can get the variable to set, and the value of it in the watch window will show something like $A$20.

Is there a way, similar to adding to an integer in a loop, that you can add to this cell value?

I'm looking to create a growing, or changing range, that would be used like this
Code:
.Range(startcell, endcell)
(or maybe it should be startcell:endcell)

So if startcell is now $A$20, my next pass through I would like it to be $A$26 and so on. For this particular use I only need to grow the row number but suppose knowing how to do both wouldn't hurt.

I've accomplished this easy enough when the variables represent two integers that refer to the row & col. I figure there is something similar that can be done for a range figure.

Help anyone?

Range Functions - Go To Next Cell
Hello,
I am writing a sub that will generate a chart after some data is retrieved. The number of rows is not fixed. One column that I am charting (Y) contains NULLs.

I need to find the address of the last Y - this is in the same row as the last X.

Is there a range function I can use that would do the following:
after the coordinate of the last X is found (say $B$30) move over to the cell to the right of it ($C30)?

I am finding the last X as follows:

Dim lastX As String
Dim lastY As String

lastX = Range("B1").End(xlDown).Address (say $B$30)
lastY = ??? (should be $c$30)
Please help.

Change Cell Range Value
Hi guys,

I have this line in a loop and i want to increase the cell range number by one everytime i go through the loop.

x = Sheets("Main").Range(B27).Value

i.e Range (B28) and so on....

Thanks in advance

Getting A Cell Address From A Range
I am new to VBA and am looking for a way to get a cell's address for use in a program.

I'm doing this on MS Office 97.

Here's what I'm after:

I have an Excel file that is data from a mainframe system. The size of the data is variable so I don't know what the last column data appears in. I need to find a way to get the cell address (i.e. B1, C4, etc) from a range after I've used the CurrentRegion.Select code. The Select code works fine but I can't get it to give me the address of the last cell in the selected range. Any ideas?

Thanks!

Finding The Range Of A Cell
If I search for a name in Excel, how do I get the Range of the cell once the name is found?

For example, I search for for John Doe and it is located in Range("A22"), how do I assign A22 to a variable? How will I later increment A22 to A23 and so forth? Thanks for any help.

Select A Cell Range Using VB?
Hi all,

I have a huge sod off sheets of data, but I only want to extract some bits of it. I am using FIND via VB to locate the start of the data segment I'm after, which works fine. The problem I am having is how to get VB to select from that cell that it has just found to 26 cols accross and 50 rows down, so it can then copy all that data and send it to another sheet.

Normally I would just say Range("A3458:Z3508").select - the problem with that is the data I am after may not always be in the same place on the sheet - hence using the FIND.

I have tried with statements, offset & selection meathods, but all of them move the active cell focus to the end - it does not select all the range, just the 1 cell.

Any Ideas anyone.

Cell Member Of Range?
I'm working with VBA and excel, and what I'd like to do is test if a cell is part of a given range.

A B C D
1
2 X
3
4

So now, if we select B2 - we could do something like
If selected cell is part of A1:C3
Then

That's what I'm looking to try and do....any suggestions?

Specifying A Range Within A Macro
The following code, which works fine, sets a destination range in a specified worksheet as Cell(rnum,1). So the row is rnum, which is defined earlier in the code, and the column is A:

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,1)
End With

What I want to do is set the destination range as a range from Cell(rnum,1) to Cell(a,1)...where a is defined earlier in the code. Here's what I've tried, but it hasn't worked...Any suggestions? Feel free to PM me.

With Column("A")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(.Range(.Cells(rnum,1),.Cells(a,1)))
End With
End With

Export Cell Range To Excel
Hello,

Is it possible to export a range cells from a sheet to another sheet?

I know how to export a sheet to another sheet but really don't know how to include a cell range.

Thanks in advance,

Jaap.

Getting The Row Index Of A Single Cell Range
I have a single cell that is named: "NamedCell"

I want to retrieve the row number of the cell

I have to think this is simple but I haven't found the right method.

Help?

Select Specific Cell Range
Microsoft Excel 2003
Microsoft Visual Basic 6.3

Hello,

I am trying to make my macro select a range of cells whose row numbers are calculated within the macro.

Unfortunately the method I used doesn't seem to work, it tells me there is a 'Run time error 1004' and the debugger goes to the offending line (the second line of code shown below).

I originally recorded the macro, and then edited it to be able to automatically select a different range.
I originally had:

Range("B34:B37").select

And changed it to:

Range("B[(p + 1) - (r - 1)]:B[p + 1]").select

p & r are both defined beforehand and set equal to numeric values which depend on certain other factors.
This is probably a ridiculous way of going about it and I know very little about programming, but if anyone knows the correct way then I could REALLY use some help.

Thanks,

Henry

Is There A Prettier Way Of Checking Which Range A Cell's In?
I've got the following code to check which State a particular County is in (England, Wales, Scotland, or Northern Ireland). There are 5 named ranges (AllCounties, EnglandCounties, WalesCounties, ScotlandCounties and NIrelandCounties). The aim is to change the value of cmbState when a particular county is selected from the cmbCounty dropdown box. The code below works fine but It's terribly bloated for what it does. I'm just interested to know if there would be an easier/shorter approach to achieving this.

Code:
Private Sub cmbCounty_Change()
For Each County In Worksheets("data_Counties").Range("EnglandCounties")
If County.Value = cmbCounty.Value Then
cmbState.Value = "England"
End If
Next

For Each Cell In Worksheets("data_Counties").Range("ScotlandCounties")
If County.Value = cmbCounty.Value Then
cmbState.Value = "Scotland"
End If
Next

For Each Cell In Worksheets("data_Counties").Range("NIrelandCounties")
If County.Value = cmbCounty.Value Then
cmbState.Value = "N. Ireland"
End If
Next

For Each Cell In Worksheets("data_Counties").Range("WalesCounties")
If County.Value = cmbCounty.Value Then
cmbState.Value = "Wales"
End If
Next
End Sub

Many thanks,
Matt

Retrieving Single Cell Range Through ADO
Hi all!

Please allow me to call upon your expert advise one more time...

I'm currently trying to automate processing of Excel based forms. To avoid having to open the individual workbooks I would like to use ADO. Additionally, because I'm not the developer of the forms and would prefer pointing to individual cell values though range names. That way the form designer can change the lay-out without the back-end being impacted...

However, when I try to pull a single-cell range value through ADO I receive followiing error message: "Run time error '-2147217865. The Microsoft Jet database could not find the object 'RangeName' ". When using ADOX to check the (Excel) table design, the range is not being listed either.

If now I extend the range to include 2 cell, even next to each other, the error will NOT occur! I'm puzzled...

Does anyone have a clue? I'm using Excel 2003 and ADO 2.5.

Thank you in advance!
Cheers, Edgard

Selecting A Range {until Empty Cell}
Hi

I'm trying to write code that will select a range of cells (in column A) but will stop selecting once an empty cell is detected.

I'm not sure if this code should include a do-until loop?
I would greatly appreciate any help.
Thanks

Show Cell Range Selection Box
Hello All,

I was wondering if there is a way to call that little cell selection box that is used for selecting cell ranges? If not, is there a control I can put on a UserForm that will allow this function?

Thanks,
Jason O

Compare Cell Results In Range
Hi guys,

I have written a macro that calculates several values and returns this data into two ranges. What I need to do is then compare the results from the two ranges and return the difference between the two results. Here's an illustration of what I am trying to do.

HTML Code:
A B C D
foster 5 drain 18
drain 21 jammer 25
linder 18 locker 20
jammer 31 linder 17
locker 22 foster 7
I am trying to match columns A and C, and subtract the value in column D to the respective value in column B. Since the data in Column A and C is not in order, I need a way to perform some sort of lookup so the correct values from Column B and D are subtracted. As an example, the result for Foster would be 2. Foster's Column B value will be subtracted from Column D and the result would be 2.

The results from subtracting column B from D can be output into a different range on the page with the name from column A.

Please let me know if you can offer any assistance or direction. Thanks in advance for your help!

Selecting Cell Range By Clicking It
Let's say I'm making a chart. At some point I'm asked for the range of data to use. I can either type in a cell range, e.g. "=Sheet1!$A$5:$B$10", or I can click that little icon to the right of the text box and then simply click on the cell range I want. Easy peasy!

I want my macro to do exactly this. I want my user to start running the macro, and be asked in a dialog box for a cell reference, at which point they can just click on the cell they want to use (which can be on a different sheet).

Can this be done, and if so, could someone point me in the right direction? Thanks a lot,

Problem With Cell/range Selection
I'm sure this is an easy one for you VBA gurus, but is frustrating me that I can't quite get it to work, so I hope you can take a minute to help me.

I need to go to the last column with data (row A has all the headers, so the last value in row a, right?), and in the next column do the following:

1. Add the header "Normalized Hours"
2. In the rest of that column (where there is data - column a is always populated), insert the formula =f2*c2

As I said, this is probably easy, so I look forward to your heckling!

Thanks in advance!

Don

Populate Listbox From Cell Range
Hi all,

I have a VB6 form with a OWC spreadsheet component embedded into it.

how can i populate a listbox with all used cell values from cells A2 & A11.?

Thanks for any help as im a bit stumped.

Dan

VBA: Select A Random Cell With In A Range?
Thanks for your help.

Here is the task at hand

Code a vba macro to:
1. Choose a range of cells.
2. Choose (n) random cells within the given range.

Thanks

-Straub

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

How: User Should Select Cell Range ?
simple question : how is the function call, to let a user select a cell range (by mouse)?

Example : if you add new data to a graphic, you will be asked to select the cell range with the new data by a small selector, where you may type in the coordinates or click on a field in the right to select by mouse.

Any hints are appreciated.

How Do I Find An End Cell In A Changing Range
In Excel i have a data range from A1 to C234. The number of rows in this range can vary. In a macro I want to go to Cell D234. ie go to the last row and move right to the first blank cell.
eg if the data range is A1 to C500. I want to get to Cell D500
Thanks
Ollie

Force Caps In Cell Range
I have a range of cells (D6200). Anytime ANYTHING is typed within this range, I want it to be in capitols. How do I do this?

MS Excel - Selecting A Range Using The Last Cell
Hi! Can you help with this problem... it may seem really simple, but I'm new to VB so it's proving to be a little tricky (for me!). Basically the problem is this; using code I've written a VB macro that will present data in a more "visually friendly" format. This is done in Excel. The only thing I'm having problems with is border style. The thing is the macro is to be used on many different sources of data and there will never always be the same number of records (rows) in the spreadsheet each time. Therefore, what I'm trying to do is select all of the cells up to and including the last cell and then format the cells so that they have a border. At the moment I've had to define a range and then afterwards I manually delete the excess cells. Is this possible?

If you can help, I'd be very grateful... not to mention relieved in not having to manually delete cells I don't need.

Thanks.

How Do I Store The Range Of The Active Cell
In Excel VBA, what's the code to store the range of the active cell as a
range variable?

Fred

Select Cell Range Using For Loop
I'm trying to select a range of cells all on one line using the command

Range("C:G" & w).Select

where w is the for loop count variable. I keep getting a 1004 error so I replaced the C:G with one statement for each cell (C, D, E, F, G) which is ugly but works:

Range("C" & w).Select
...
Range("D" & w).Select
...
...
Range("G" & w).Select
...

Is there a way to modify my original code to achieve the same goal using about 30 less lines of code?

Help Converting A Cell Value To Range Address
Would these poweful programing minds kindly enlighten this NewBie :
I have concatenated a column number and a row number using two InputBox
apps and placed the text in a cell way down the sheet . Now I need Excel to read
that value as a range value. Both InputBox variables have been declared as String variables . I can rename that combined text as a new variable and then use another input box and have the new combined variable as the "default:=" value and further Type:=8 , making it a cell address and yes the cursor moves to that position ; but I havent figured out an easier way to just programatically read the Cell.Value as A Range . Is there a way? What am I missing?
All help more
than appreciated.
Thanks a million

Edited by - goldtuth on 1/27/2004 11:55:57 PM

Live Life Fast Cause Time Slows Down And We Get More Of It!!!!!!!!! Do Get Have Now

New Help Determining If A Cell Is In A Named Range
I need help. I am working on a spreadsheet. I have two buttons on spreadsheet that allows a user to either insert a line or delete a line. I need to capture the cell that the user is in when they press the button. Based on if that cell location is in the named range (staffdata), then I want to allow them to either delete the row or insert the row. If they are outside of the named range, I want to send them a msg box explaining the error. Can anyone help me? Thanks in advance

Jim

Dynamically Get The Range In Macro
Hi everyone,

My question is I need to use a macro to dynamically select all the non_blank cells (it woulld be like A1:D15 that always starts with A1 and ends with Dxx). The purpose is I will need to make a chart out of this range of data in macro.

I am very new to VBA. Can anyone help?

thank you very much

rita

Macro With A Variable Range
Selection.AutoFill Destination:=Range("F2:F104")
Range("F2:F104").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

I am a total newbie with VB, I know enough only to be able to read the code and decipher what it's doing. My issue is that I want the range -Range("F2:F104") - to start at F2 but fill in the formula for all rows. The worksheet has variable # of rows month to month. I could just put in a number larger than I'll ever have rows but it's time for me to learn a new trick. Thanks in advance for the help.

How Do I Select A Range Relative To The Active Cell?
I'm trying to select a range relative from the current active cell either using variables or constants, I don't want an absolute range because I want to loop the operation I'm trying to do several times in a spreadsheet...how do I do it? Thanks.

Finding Last Cell With Data To Enter In Range
I need to find a way to tell a Macro to autofill a range with a formual that is contained in say Cell B3. I want to copy that Formula only to all the cells below only if the Cell in Column A has data. So one time I may have data in 40 Rows at another time I may have data that in Column A has data that goes to 300 Rows. Can you tell me how to write the Formula that will do this?

I need as much explainantion as possible.

Thanks in advance

Mike