Visual Basic :: Naming An Excel Range

Naming Worksheets In Excel
I'm looking to import data into a worksheet and i need to rename the worksheet dependent on the name of the file being imported.

for example: if i import 'test' i need the worksheet it imports onto to be callled 'test'

but i don't want to have to create coding for each individual report, simply because we need users to add their own imports and such.

any ideas?

VB Code:
Sub Auto_Open()'' Macro1 Macro' Macro recorded 25/08/2005 by User ' Deletes Sheets 2 and 3 in Workbook Sheets("Sheet3").SelectApplication.DisplayAlerts = FalseActiveWindow.SelectedSheets.DeleteApplication.DisplayAlerts = TrueSheets("Sheet2").SelectApplication.DisplayAlerts = FalseActiveWindow.SelectedSheets.DeleteApplication.DisplayAlerts = True ' Imports Data into Sheet 1 With ActiveSheet.QueryTables.Add(Connection:="TEXT;C: est.csv", Destination _:=Range("A1")).Name = "test".FieldNames = True.RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.RefreshStyle = xlInsertDeleteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.TextFilePromptOnRefresh = False.TextFilePlatform = 437.TextFileStartRow = 1.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileTabDelimiter = True.TextFileSemicolonDelimiter = False.TextFileCommaDelimiter = True.TextFileSpaceDelimiter = False.TextFileColumnDataTypes = Array(1, 1, 1).TextFileTrailingMinusNumbers = True.Refresh BackgroundQuery:=FalseEnd With End SubEdit/Delete Message

Excel Range In Variant Array, 'Subscript Out Of Range' Error
Morning Guru's

I have been having problems with this function for a few days now. I'd be most grateful if one of you could set me straight with this. Here is my code:

Code:

Private Function RemoveNulls(ByVal srcRange As Excel.Range, ByVal lastRow As Long)
MsgBox srcRange.Address

Dim i As Long
Dim xlVals() As Variant
xlVals = srcRange.Value
For i = LBound(xlVals) To UBound(xlVals)
If xlVals(i) = -9999 Then xlVals(i) = 0
Next i
srcRange.Value = xlVals
Set srcRange = Nothing
End Function

The compiller gives me the 'Subscript out of Range' error on the line:
If xlVals(i)=-9999 Then xlVals(i)=0

In-fact if I try to get a msgbox or something to display any value in the array, I get the same error. The Lbound and UBound functions are returning the expected values, therefore I believe that the array definately contains data!?

Any Ideas Amigo's?

Thanks

Saving An Excel Doc And Naming It Automatically
Dear VB Gurus! (Good start eh?)

I have an excel doc used as a form to collect lots of data about a person.

Three pieces of critical infor are Forename, Surname & Staff Number.

I have already setup a macro that will ensure these fields have been filled in correctly but I now want to be able to save the document to a particular locatiobn on the users PC (My documents/Comp Assurance Profile). This has presented me with two problems: -

1. How can I get VB to look for a folder in "My Documents", Comp Ass Profile, creating it if it's not there.

2. How can I get VB to create a file name from "SurnameFornameStaffID" without input from the user.

Any help you guys can provide would be gratefully received.

(How many posts rid me of the "Newbie" tag?!)

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.

Excel Automation / Naming Workbooks
How do you name a workbook using Excel automation without using GetSaveAsFilename, or any of the save methods? The Workbooks.Name property is read only so I am hoping there is another way.

Thanks!!

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 Tabs In Multipage Using Excel
Hi

I have no idea how to get data from excel to show in vba forms. Please help

Specifically, I would like to know if it's possible to name multi-page tabs using data in a table. e.g. I have a list of employees and their employee numbers. I have a multipage with enough tabs for each employee and I would like to know if there's a way to make the tab caption read with the employee name. That way, all you would have to do to add a new employee is put their name and number in the spreadsheet and the form would change automatically.

Possible?

Excel: Naming Worksheet Tabs
Hi,

I would like to name worksheets based on certain data input into specified cells in Excel using VBA. How would I go about getting excel to check if the name already exists in that workbook? And if it does, to name it like the copy function does...i.e. worksheet (2) or worksheet (3)...etc automatically. Currently, I have to trap that error and then work around it....is there a more efficient way?

Thanks.

Problem In Excel Sheet Naming......
PLz help me........

I am using an Excel sheet which i am accessing as Worksheet(1).Cells(3,3)(Say 3rd row and 3rd colom)........But once a new sheet is addedthe same sheet has to be accessed as Worksheet(2).Cells(3,3) and the new sheet becomes worksheet(1)........

In other Sheets i didnt encountered this problem.....So i want to access them as thier name.......is there any solun?????????

Amitt

Excel Visual Basic, Naming Cells
Hello

I recorded a macro to name a cell, do functions elsewhere in the worksheet, return to that named cell, and then delete that name so I could start the process again from another cell. However, the active cell ends up somewhere else and not at the named cell specified in the macro. Our IT section suggested a relationship to the “ActiveCell” command in the macro edit box, i.e., the “ActiveCell” needed to "recognize itself" as the named destination. Thanks for help.

yumatom
Yuma, Arizona

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?

Excel: Named Range Will Not Accept New Range Value
I have 2 worksheets in the same workbook
On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
This range value changes due to specific triggers which aren't important
In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

The call to the function looks like this:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")

The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
                                ObjWkSheetName As String, InfoType As String, _
                                IRNumber As String, RangeInfoCell As String)

Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
Dim PivotSourceData As String
Dim NameRef As String

    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet

    PivotTableName = "PIR" & IRNumber & InfoType    ' PIR1DTCAT

    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then    ' C4:L33
        'There is no downtime data, hence the pivot table will not be updated
    Else
        PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value    ' 'PIR-DT MTH'!C4:L33
        NameRef = "PIR" & IRNumber & "DB"    ' PIR1DB
        myworkbook.Names.Add NameRef, PivotSourceData    'This is the line which doesn't work
        objworksheet.PivotTables(PivotTableName).PivotCache.Refresh

        myworkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
    End If

End Sub

Changing the Named range doesn't work
The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
I have no clue where it gets this from
And everytime you run the code, the strange range changes
Even when I hardcode the value (see below) , it still doesn't work
myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
Am I using any predefined VB words?
Is the sky falling?

RESOLVED: Finding A 'range' From Within A 'range'? - Excel VBA
Relative newbie...

Wanting to get coding that will perform a search through a spreasheet range to find a unique row of data (i.e. a match to the contents of first 3 columns), returning the row No. where the match is found. I had thought that a 'range.find' would accomplish this, but have since been advised otherwise.

Anyone any ideas????

Adjacent columns in the row are for additional pertinent data. The search will be used either to retreive the additional data, or, to add to or change it.

thnx in advance,
remalay

Edited by - Remalay on 8/22/2006 6:50:32 AM

Excel Named Range --> Vb Range
Hello,

I have an excel spreadsheet which has a named ranged which defines a table.

in vb.net i want to open the workbook goto the sheet (this i can do) then copy the table using the named excel range stored in the worksheet and then paste this table into powerpoint.

I do not know how to refrence the named range in excel. is it part of the worksheet property? i have not been able to figure that out.

i also might have problem with the pasting into powerpoint. when i paste the data will the grid from excel show up or can i paste it in as a picture?

Thanks

Vb/Excel: How To Read An Excel File In A Column WITHOUT Specifying The Range ??
Hello, all.

I found a bit of code that lets me read an Excel file from and to a specifi
range. See the code below. But what if I only want to specify the STARTING
position and simply want it to continue reading down the column until it
hits an empty cell?

Does anyone know how to do this? Thanks!

Here is the range-only code:

' ** turn screen updating off, prevent the user from seeing the source
workbook
' ** then find the file and identify both the worksheet and range
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open(file, False, True)
listitems = sourcewb.Worksheets(sheet).range(temp_range).Value

' ** close the source workbook without saving changes, then reactive
screen update
sourcewb.Close False
Set sourcewb = Nothing
Application.ScreenUpdating = True

' ** read the data, convert values to a vertical array
listitems = Application.WorksheetFunction.Transpose(listitems)

For count = 1 To UBound(listitems)
' ** read the info into a temporary field
temp_var(count) = listitems(count) ' ** populate the array
Next count

' ** no items selected, set to 0 to select the first item
'Combo_machine.ListIndex = -1

Close Workbook

Copy Range From Excel To Excel From Within Excel?
Hi,

I want a user to be able to click on a button on a sheet in Excel which would then copy a range of data from that worksheet into another worksheet in a new workbook.

Could someone give me an idea on where to start?

Regards

I Have An Excel Range ?
hey gang- I can't seem to make these ranges do what I want them to! Would someone mind taking a look at this macro and slap me with the right answer?

Code:
Range("RegisterList").Select
Selection.Resize(RegRowCount + 1, RegColCount).Select
ActiveWorkbook.Names("RegisterList").Delete
NewAddress = "=Register!" & Selection.AddressLocal
ActiveWorkbook.Names.Add Name:="RegisterList", _
RefersToLocal:=NewAddress

this defines the range name, and shows it in the Excel list, but for some reason, when I need to call the name from a different form,(i.e. to load a combobox on a different form) I get an error telling me the name is not valid.

help?

Excel To VBA, MAX/MIN From Range
Please refer to the attachment.

To extract the maximum value from column B that corresponds to a specific time, I’m using the cell formula

=MAX(IF(A$3:A$30000=726,B$3:B$30000))

(=MIN is substituted to find the minimum value).

The formula above is repeated approximately 700 times in cells/rows corresponding to each 1-minute increment (from 727, ie 7:27 am, to 1900).

I think a VBA solution would be more efficient. Perhaps someone could make a suggestion.

Thank you.

Grant.

Copy Range From One Excel File To Another Excel File In VB, Work Once, Fail Rest?
Hello, everyone:
I tried to copy some ranges from one excel file to another in VB. I used the early binding. So, it encounter a problem: worked once, failed after. I knew that something wrong to reference Range. However, I don't know how I can correct to refer it? Any suggestion will be very appreciated. Thank you all in advance.
Code:
Set xlApp = New excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open xlsfilenameOpen

xlApp.Worksheets("sheet1").Activate
With xlApp.Workbooks("LogView.xls").Sheets("sheet1")
Range("a" & Format$(dateToRow) & ":r" & Format$(dateFromRow)).Copy
Set rng = Range("a" & Format$(dateToRow) & ":r" & Format$(dateFromRow))
End With

xlApp.Workbooks.Open xlsfilenameStore
xlApp.Workbooks("Lookup.xls").Activate
xlApp.Worksheets("sheet1").Activate
nLastRow = xlApp.Worksheets("sheet1").Cells(65536, 1).End(xlUp).Row
With xlApp.Workbooks("Lookup.xls").Sheets("sheet1")
Range("a2:a" & Format$(nLastRow)).Select
Selection.EntireRow.Delete
Range("a2").Activate
Range("a2").Select

Set rng1 = Range("A65536").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1

End With

Proper Way To Use Range In Excel?
In my VB app I need to select the first 13 columns for the row I'm currently on and write them to a new XL file. How can I use Range to select the columns to copy, and then use Range to put them in the same spot in the new table? I have the original sheet as well as the newly created sheets open, I just need to get rows that meet certain criteria.

something like:
ActiveSheet.Range(iRow1,iRow13).Select ? iRow being a counter for looping and keeping track of what row I'm on. If this works, then how can I dump those into the new sheet?

Thanks.

Selecting A Range In Excel
I have this really long macro that looks like this:

Code:
Sub Macro1(lquota As Long)
Workbooks.Add
Sheets("Sheet1").Select
Columns("B:B").ColumnWidth = 8
Columns("C:C").ColumnWidth = 25
Columns("D:D").ColumnWidth = 12
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "DATE"
Range("C1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "RESP. #"
Range("D1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "VFY.IN"
Range("A1:D54").Select 'this part here
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("a1").Select
Dim i As Long
i = 2
Dim temp As String
For i = 2 To lquota
Cells((ActiveCell.Row + 1), ActiveCell.Column).Select
ActiveCell.FormulaR1C1 = (i - 1)
Next
ActiveWorkbook.SaveAs FileName:="c: emp.rcd", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
the part that says
Range("A154").Select
I want to make variable from A1 to D?
how can I do this. Thanks
Rob

Range Functions Using VB In Excel
Hi folks,
I'm looking for some help with range functions using VB in Excel. My program goes down a column checking cells for a value. There are 7 columns involved, A to H, and I am searching the E column. If that value is found, then I must cut&paste or at least copy that entire row. The problem is that row numbers are not known, and I can not seem to get any range functions to work to anchor at column A and end at column H or just selecting the entire row would be fine too. Any help would sure be appreciated.

Excel Unnamed Range + ADO
Okay, I'm embarrassed, because surely I am just missing something simple here. All the documentation I've read says that to specify an unnamed range in Excel using ADO, you just use the standard range notation after the sheet$ in the select statement. But I'm consistently getting an error: "The Microsoft Jet database engine could not find the object '...'. Please help me spot what I've missed. Here is my very simple code:

Code:
oSrcConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & _
";Extended Properties=""Excel 8.0;HDR=No;"""
sSrcSQL = "SELECT * FROM [Phone Performance Summary - New$A14:W15]"
oSrcRS.Open sSrcSQL, oSrcConn, adOpenForwardOnly, adLockReadOnly, adCmdText

This works fine if I omit the A14:W15 bit, but I need to restrict it to just that range. It also works fine if I take the "-" out of the sheet name and leave the range, but renaming the sheet isn't an option since this is being provided from a source we have no control over. Ideas?

Excel-Chart-Range
Hallo
I'm a newbee and i want to make my visual basic programm to create a chart using for data columns that are not adjacent.

I'm sorry if this question is trivial ,but I would appreciate an answer

Assingning Range In Excel
Hi,
I would like some help to assigm a range selection to a variable and be able to recall the range when required. The range would be different as opted by the user. so probably an input box or something that can capture the range given by the user.

-JK

How To Sort Range In Excel Thru VB
Dear

http://www.vbforums.com/showthread.php?t=385142
Hi I just had gone and seen the above result. Quite difficult to understand.

Basically in my excel worksheet there are basically 8 coulmns
I want to sort the full column no 8 i.e "H" and with this the coressponding columns from "A to G" with the corresponding data of column "H".

Column "H" bascially contains the data different "DATES"
Is it Possible to sort the range "DateWise"

Thanks in advance

regds

Samyo

Sort Range In EXCEL??
How do you sort a range in excel, and do I need to do something different because I am sending the recordset straight to excel and then telling excel to sort.

This does not work. Any ideas?

VB Code:
Private Sub Command3_Click() Dim xlsApp As Excel.ApplicationDim currentRow As Integer, currentColumn As IntegerDim rowplus currentRow = 1 currentColumn = 1 rowplus = currentColumn + 1 Set xlsApp = Excel.Application With xlsApp 'Show Excel .Visible = True 'Create a new workbook .Workbooks.Add 'Put text in to the cell that is selected .Range("A1").Value = "PNE USERS" .Range("A2").Value = "UserID" .Range("B2").Value = "Employee Name" .Cells(currentRow, currentColumn).Font.Bold = True .Cells(currentRow, rowplus).Font.Bold = True End With Set objCommand = CreateObject("ADODB.Command")Set objConnection = CreateObject("ADODB.Connection")objConnection.Provider = "ADsDSOObject"objConnection.Open "Active Directory Provider"objCommand.ActiveConnection = objConnectionstrBase = "<LDAP://ou=cse,dc=my,dc=server,dc=com>" strFilter = "(&(objectCategory=person)(objectClass=user)" _& "(userAccountControl:1.2.840.113556.1.4.803:=65536))" strAttributes = "sAMAccountName,cn"strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"objCommand.CommandText = strQueryobjCommand.Properties("Page Size") = 1000objCommand.Properties("Timeout") = 30objCommand.Properties("Cache Results") = True'Set objRecordset = objCommand.ExecuteSet objRecordset2 = objCommand.Execute Set objRecordset = New ADODB.RecordsetobjRecordset.CursorLocation = adUseClientobjRecordset.Open objCommand, , adOpenStatic, adLockReadOnlyobjRecordset.Sort = "cn ASC" xlsApp.Range("A1:B1").Merge Do Until objRecordset.EOF strName = objRecordset.Fields("sAMAccountName").Value strCN = objRecordset.Fields("cn").Value strSpace = vbCrLf strOutputData = strCN & ", userID: " & strName Text1.Text = Text1.Text & strOutputData & vbCrLf objRecordset.MoveNext [b]xlsApp.Range("A1").Value = "USERS WITH PNE"[/b][b]xlsApp.Range("A5").CopyFromRecordset objRecordset[/b] xlsApp.Columns().AutoFit 'End With LoopobjConnection.Close Exit SubEnd If

How To Sort A Range In Excel Thru VB?
I keep getting the error "Sort method of range class failed" Run-time Error 1004. Does anyone know why this is happening? It worked fine when I early binded the Excel reference but now that I have bound it late, it doesn't want to sort. All my other code works fine i.e. xlApp.Range, xlApp.Sheets(), xlApp.Visible.

Here is my code:

VB Code:
If i > 2 Then With xlApp .Range("N2").Activate .ActiveCell.Value = "=Sum(C2:M2)" .ActiveCell.Select If i > 3 Then .Selection.AutoFill Destination:=.Range("N2:N" & i - 1), Type:=xlFillDefault End If .Range("A2:N" & i - 1).Select .Visible = True .Selection.Sort Key1:=.Range("N2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .Sheets("Top 5 Charts").Select End With End If

Specify Range In Excel Through VB(RESOLVED)
I am working on creating a report in Excel. When certain conditions are met I want to be able to change the font for a range of cells within a row. I know how to change individual cells as shown below, but I know there has to be a way to select a range. I would like to perform the following only as a range instead of specifying each individual cell.

VB Code:
xlSheet.Cells(row, 1).Font.Size = 8xlSheet.Cells(row, 2).Font.Size = 8xlSheet.Cells(row, 3).Font.Size = 8xlSheet.Cells(row, 4).Font.Size = 8xlSheet.Cells(row, 5).Font.Size = 8xlSheet.Cells(row, 6).Font.Size = 8

The above of course works but I would like to cram all of that into a range or something.

I tried the following just taking a stab at it, but of course it didnt work.

VB Code:
xlSheet.Range(row, 1:row, 6).Font.Size = 8

Any help would be greatly appreciated.

Select Range In Excel With VB6
Guys

Can someone please remind me how to select a range in Excel? I know how many columns there are but the no. of rows will vary. I know there's a simple way of doing this and I'm sure I've done it before. It's just that I have the memory retention of a amnesic goldfish with altzheimers.

Cheers

EXCEL: Sum Of Range Only If Next Column Is Right Value
I have values from A1 through A5. I'd like to get the sum of that range, but only for the cells that have "RED" in the next column (over).

Ex:

-----A ----- B
1---50.0---BLUE
2---25.0---YELLOW
3---30.0---RED
4---65.0---RED
5---40.0---BLUE
6---TOTAL---95.00

I'd like to do this through a formula if at all possible.

- Thanks anyways Bruce

Print Range In Excel
How can i set the print range in excel so that it will always print 1 page, if i use printArea it prints 2 pages instead of 1. Is it possible to always print to 1 page?

VB Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$R$55"

Excel Range To Word
I want to be able to from a Word Macro copy a Range of Cell Values from Excel and paste them back into Word. I can't get word to read any values from excel.

I also want to paste from excel to a word datasource / mailmerge.

When using the saveas code in word, the user is not prompted with an overwrite error if a file already exists, how I do remedy this.

Thanks.

In Excel, How Can I Put A Range Into An Array?
Can you tell me how I can put a range into an array? For example, I have an array - testarray(5,4). I want to fill it up with 5 different rows of 4 cells. I can do it the slow way of filling each element individually. But I want to fill each row at once. I've tried this:

sub test()
Dim testarray (5,4) as variant
Dim testrange As Variant
testrange = ("A11")
testarray(1, 1 to 4 ) = testrange
end sub

It obviously does not work.
Any ideas??

Thanks

Guy

Excel - Range Name Exist?
How can I determine if a named range exists on a worksheet? I'd rather not use 'Listnames' method. Thanks.

Range Method For Excel In Win XP
The Range method in Exel Object, stoped working in Win98 after compiling the project in Win XP. In XP works well, but in Win98, not even in deseign time, now.
Does anyone know why ?

Thanks

Excel Dynamic Range ?
I have seen this done somewhere..

Can i have a a range object that will grow with my expanding
list..

like myrange = range c3 to range EOF

??

That is all
Seahag

Setting Range In Excel
When using Range(cells) in excel, how do I increment the range in the column direction. for example

The user will provide a integer(sz) and I need to select the range of cells from

Range("D4: (D+sz)4").select

I know the above code doesn't work, it is just to show what I need.

How do I increment the letter portion of the Range of cells?
Do I have to write a function that cycles through the alphabet or is the a something I am missing?

Thanks
Nevin McChesney

Value Of A Range Of Cells In Excel ???
Hi all.

I just need one line for VB. It is just to say if any of the cells in a range have data in them, then.....

If (Range of Cells).Value <> "" Then.........

But this isn't right. Is there one line in VBA that means the value of any of the cells?

Thanks

Wengang

Selecting A Range In Excel
Okay, I am working with an excel document and I have an input box coming up asking for a range, I have the string for selecting the range, but I need that put in to a variable, what kind of variable does it need to be? Nothing seems to work! Please help.

Thanks in advance

Selecting A Range In Excel
I am trying (unsuccessfully) to take a value from a cell in Excel and use that value to determine how many cells in a column to select.

For Eg. If A1=10, I want to select Range("C10:C 10+10")

I have tried

CellNo = 10
cellchk = "$C$" & CStr(CellNo)
Range(cellchk).Select
Selection.AutoFill Destination:=Range("C10:"$E$" & CSTR ......
............ (CellNo)"), Type:=xlFillDefault

But this doesnt work.
It works fine when selecting a 1 entry range

cellchk = "$C$" & CStr(CellNo)
Range(CellChk).Select

but not when selecting a multi entry range.

The program I am working on feeds its values into Excel to sort results, produce graphs etc.
Hopefully someone has come across a similar problem and can help me out.
Cheers.

Excel Given Range Blinking
I am looking for a way to let a range of a worksheet background blinking.
The blinking should be started from the VB code while opening a messagebox and stopped by pressing OK on that messagebox.
Who knows a way?

Thanks in advance,
Apestaart

EXCEL:get Range Selection
Hi,
I need a code that checks if a user selects (highlight) the range A1:C1 and A6:C6 of an workbook.
I try solve this using select and selection methods but nothing.
Please help!
Thanks in advance

Excel - Searching For A Value In A Range...
Hi all - First Post!!!

I'm new to VB and need a bit of help if you would be so kind!

I'm trying to write some code that looks for the value of a particular cell ("B3") in a range of cells. If it finds it, it asks if you want to replace the data. If it doesn't find it, it pastes the data in the next blank row.

The code is below and it works just searching a single cell ("B10") but I'm not sure how to make it look at more than that one cell. ErrorMsg is the name of another macro....

Any help much appreciated (I'm sure it's just a Newbie schoolboy error!)

Cheers

-----------------------

Sub Ross()

Dim D As Worksheet
Set D = Worksheets("Data Store")

D.Activate
D.Range("B3:Ao3").Copy

If D.Range("B3") = D.Range("B10") Then
Errormsg
Else
Range("B7").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

End If

End Sub