Re: Range Copy & Paste Values Only
Perfect, Thanks!
Re: Range Copy & Paste Values Only
Perfect, Thanks!
Re: Set Variable To Named Range
Thank you, shg and daniel.c.
I don't quite understand your instructions, and I'm pretty sure I've just been unclear. So here's a fuller view of what I'm trying to do.
Say I want to have a drop down on my chart that lets you toggle between different students' grades. The chart is built to point to a static set of cells on a ChartData sheet, so when I change the drop-down, this static set should get replaced with a different set of data, based on what was selected in the drop-down.
In this example, the drop-down is a named range called "student".
If Not Intersect(Target, Range("student")) Is Nothing Then
Dim chartScores As String, student as String
dim Rng1 as Range
student = Range("student")
chartScores = student & "_Scores"
Rng1 = Range(chartScores)
...
End If
My hope was that when I switched from "Craig" to "Scott" in the drop-down, Rng1 would switch from Range("Craig_Scores") to Range("Scott_Scores")
Re: Range Copy & Paste Values Only
THanks jamie, but I tried smacking that very text onto the destination range object, and I'm getting syntax errors:
Rng1.Copy Rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Is it obvious to you what I'm doing wrong?
Re: Test If Named Range Exists
Thanks for your help! Works great.
Re: Test If Named Range Exists
I'm sorry about that. I really did run a search for "Named Range Exists", but now that I look more carefully at the search results, the message you found showed up on the third page (number 53 out of 61 responses). I'm not used to something so clearly relevant being so far back in the results, but I promise to be more thorough next time.
Thanks for your help--I really appreciate it![hr]*[/hr] Auto Merged Post;[dl]*[/dl]When I try the code you've given me, it only seems to work if the missing named ranges are looked for before the ones that exist. My macro looks like this:
Sub CheckRanges()
Dim rRangeCheck As Range
Dim Sections(4) As Variant
Sections(0) = "ABC" 'This one exists
Sections(1) = "DEF" 'This one exists
Sections(2) = "GHI" 'This one exists
Sections(3) = "JKL" 'This one DOES NOT exist
Sections(4) = "MNO" 'This one DOES NOT exist
For i = 0 To 4
On Error Resume Next
Set rRangeCheck = Range(Sections(i))
On Error GoTo 0
If rRangeCheck Is Nothing Then
MsgBox ("This Range: " & Sections(i) & " does NOT exist!")
Else
MsgBox ("This Range: " & Sections(i) & " DOES exist!" _
& vbCrLf & vbCrLf & _
"its address is: " & rRangeCheck.Address)
End If
Next i
End Sub
Display More
And even though Sections(3) and (4) don't exist, the checker tells me they do, and that their address is the same as the last one that was found: Sections(2), ie. Range("GHI"). But if I change the order and put the non-existent ranges first, the error code works like a charm:
Dim Sections(4) As Variant
Sections(4) = "ABC" 'This one exists
Sections(3) = "DEF" 'This one exists
Sections(2) = "GHI" 'This one exists
Sections(1) = "JKL" 'This one DOES NOT exist
Sections(0) = "MNO" 'This one DOES NOT exist
I'm uploading the excel document for your reference. Does anybody know why this would be?
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.
I've got a worksheet that I add a row to every day, right between the last date entry and the totals row at the bottom. On a similar worksheet, when I select a cell in the blank row between my last entry and the totals row, then Insert --> Row, all of my formulas on the row above are extended down to the new row. On this worksheet, they don't, and after I insert the row I have to drag down all of my formulas to the new row.
Not a big deal, but it's a daily hassle that I didn't have to worry about on the other worksheet. Is there a format setting somewhere that controls this feature?
Re: Automating Mail Merge
Sorry Dave, I was confused by the forum names: Word vs. Excel & VBA. This is a VBA question in Word, so that's why I thought it was a toss up.
In Word's VBA, I've recorded a macro of what I want to do: create a sheet of labels from a text document. When I try to rerun the macro, it tells me that one of the commands is not available, even though this command was generated by Word itself.
Here is the line that it says can't work:
And here is the context:
End Sub
Sub Take3()
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\craigs\Desktop\Andy.txt", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:="", SubType:= _
wdMergeSubTypeOther
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Name"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Address1"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Address2"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""City"""
Selection.TypeText Text:=", "
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""State_Code"""
Selection.TypeText Text:=" "
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Postal_Code"""
WordBasic.MailMergePropagateLabel
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
Display More
Any insight/help would be appreciated!
Is there a way to embed a listbox inside a messagebox? Or do I have to create a userform for that?
I'm trying to redim a multi-dimensional array, whilst preserving the values that are already there.
This command works fine:
and so does this:
but this one does not:
Do you know why it's telling me Subscript is Out of Range?
Re: MultiSelect Listbox: Counting Selections
Thanks Will and everybody for your help on this--it's just what I needed!
I've got multiselect option 2 enabled: fmMultiSelectExtended and I want to be able to count up the total number of rows the user has selected. How can I do that?
I know how to populate a listbox from a spreadsheet, but is there a way to do the opposite? I have a multidimensional listbox in a userform that I want to write out its contents to a new sheet.
Re: Ubound/Lbound Multi Dimmension Array
thanks!
Re: Redim custom types
You're awesome, your code helped me find my error. I'm glad that it works just the way it should. thanks for your help.