Excel Macro VBA Tip 2 - Using The Range Property to Select Cells in Excel

Can anyone tell me why:

Range(Cells(iNameR, iNameC + 3), Cells(iNameR, iNameC + iCount + 2)).Select

works while:

Range(Cells(iTargetR, iTarDateC)).Select

returns the runtime error '1004' method 'Range' of object '_Global'

TIA


--
remove 901 from reply email for valid address.

heidecke@901shaw.ca
-----
remove

Hi.
I seem to be going round and round in circles with this, but I'm sure it should be easy.

I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:

Dim namesTotal As Integer

namesTotal = 2500

Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select

(According to the Microsoft website, this is supposed to be the way to do it?)

Thanks in advance for your help.

HI ,everybody

i m a newbie to excel programming. i m facing problems using Cells, Range & other objects in excel vba.Please let me know how to use the basic operation of these objects in the programming.
THANKS IN ADVANCE..............

I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?

Hi
I am trying to link an Excel 2003 spreadsheet to a Word 2003 document But I would like to include a textbox which occupies the top of the spreadsheet. I can't select the textbox as well. Microsoft's Help system doesn't provide the advice. Microsoft's Help System could suggest whether this is possible or not. Well like other MS Helps one resorts to an Internet search ( I should have gone to Mr Excel first).

Can I select a range of cells including a textbox which is at the top of the spreadsheet?

I have a variable data in Range D1 to D10. I need a macro that selects only the cells that has the data. (Since the range is variable)

Eg1: if i have the data from D1 to D3, then the macro should select only from D1 to D3.

Eg2: If i have the data only in D1, then the macro should select only D1.


Your help is very much appreciated..

I need to clear the contents of a certain range and yes I could just write the code as follows: Range("a1:b240").clearcontents

however, the range selection depends on the active cell. I have tried entering the following: Range(activecell, activecell.offset(1,240)).clearcontents
But I keep receiving a runtime error '1004'. Any ideas on how to fix this? Thanks....AJ

Hi!!

So in my sheet, I have selected column D. And I want to select the next 4 columns (including column D), so that my selection or active cells at that point would be Columns D, E, F, G and H.

All help is very appreciated! THanks!

Please help me!!! I have the follow code and it keeps giving me the error code 1004 - unable to set the locked property of the range class.

Please help me! This is due ASAP!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="kathyrocks"
If Range("E15") = "Custom Term" Then
Range("G15").Locked = Flase
ElseIf Range("E15") = "12 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E15") = "24 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E15") = "36 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E15") = "48 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E15") = "60 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E15") = "Please select a package or choose custom term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G15").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
End If
If Range("E17") = "Custom Term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G17").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E17") = "12 Month Package" Then
Range("G17").Locked = True
ElseIf Range("E17") = "24 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G17").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E17") = "36 Month Package" Then
Range("G17").Locked = True
ElseIf Range("E17") = "48 Month Package" Then
Range("G17").Locked = True
ElseIf Range("E17") = "60 Month Package" Then
Range("G17").Locked = True
ElseIf Range("E17") = "Please select a package or choose custom term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G17").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
End If
If Range("E19") = "Custom Term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "12 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "24 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "36 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "48 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "60 Month Package" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E19") = "Please select a package or choose custom term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G19").Locked = True
ActiveSheet.Protect Password:="kathyrocks"
End If
If Range("E21") = "Custom Term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G21").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E21") = "12 Month Package" Then
Range("G21").Locked = True
ElseIf Range("E21") = "24 Month Package" Then
Range("G21").Locked = True
ElseIf Range("E21") = "36 Month Package" Then
Range("G21").Locked = True
ElseIf Range("E21") = "48 Month Package" Then
Range("G21").Locked = True
ElseIf Range("E21") = "60 Month Package" Then
Range("G21").Locked = True
End If
If Range("E23") = "Custom Term" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("G23").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("E23") = "12 Month Package" Then
Range("G23").Locked = True
ElseIf Range("E23") = "24 Month Package" Then
Range("G23").Locked = True
ElseIf Range("E23") = "36 Month Package" Then
Range("G23").Locked = True
ElseIf Range("E23") = "48 Month Package" Then
Range("G23").Locked = True
ElseIf Range("E23") = "60 Month Package" Then
Range("G23").Locked = True
End If
If Range("J15") = "Custom Name" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("L15").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("J15") = "Basic" Then
Range("L15").Locked = True
ElseIf Range("J15") = "Preferred" Then
Range("L15").Locked = True
ElseIf Range("J15") = "Preferred Plus" Then
Range("L15").Locked = True
ElseIf Range("J15") = "Preferred Extreme" Then
Range("L15").Locked = True
ElseIf Range("J15") = "Ultimate" Then
Range("L15").Locked = True
End If
If Range("J17") = "Custom Name" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("L17").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
If Range("J17") = "Basic" Then
Range("L17").Locked = True
ElseIf Range("J17") = "Preferred" Then
Range("L17").Locked = True
ElseIf Range("J17") = "Preferred Plus" Then
Range("L17").Locked = True
ElseIf Range("J17") = "Preferred Extreme" Then
Range("L17").Locked = True
ElseIf Range("J17") = "Ultimate" Then
Range("L17").Locked = True
End If
If Range("J19") = "Custom Name" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("L19").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("J19") = "Basic" Then
Range("L19").Locked = True
ElseIf Range("J19") = "Preferred" Then
Range("L19").Locked = True
ElseIf Range("J19") = "Preferred Plus" Then
Range("L19").Locked = True
ElseIf Range("J19") = "Preferred Extreme" Then
Range("L19").Locked = True
ElseIf Range("J19") = "Ultimate" Then
Range("L19").Locked = True
End If
If Range("J21") = "Custom Name" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("L21").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("J21") = "Basic" Then
Range("L21").Locked = True
ElseIf Range("J21") = "Preferred" Then
Range("L21").Locked = True
ElseIf Range("J21") = "Preferred Plus" Then
Range("L21").Locked = True
ElseIf Range("J21") = "Preferred Extreme" Then
Range("L21").Locked = True
ElseIf Range("J21") = "Ultimate" Then
Range("L21").Locked = True
End If
If Range("J23") = "Custom Name" Then
ActiveSheet.Unprotect Password:="kathyrocks"
Range("L23").Locked = False
ActiveSheet.Protect Password:="kathyrocks"
ElseIf Range("J23") = "Basic" Then
Range("L23").Locked = True
ElseIf Range("J23") = "Preferred" Then
Range("L23").Locked = True
ElseIf Range("J23") = "Preferred Plus" Then
Range("L23").Locked = True
ElseIf Range("J23") = "Preferred Extreme" Then
Range("L23").Locked = True
ElseIf Range("J23") = "Ultimate" Then
Range("L23").Locked = True
End If
End If
End Sub

Afternoon All,

Please Assist...If you can:

I have a some data which I have pulled through from access into Excel

I need to record a macro which will select data which is in a specific column. The problem is when a pull the data from Access the amount of rows will differ. On one occasion I may have 100 rows other times only 50. So I need a macro to go to a specific cell ie C3 and then select all the data in that column from that cell untill the last cell in that column. I hope this makes sense.

Any help will be appreciated.

Thanks,
J