LinkBack URL
About LinkBacksI don't understand how the naming ranges works in Excel VBA. Once I define
them in the spread sheet part, what are the requirements to be able to use
them in the VBA code? I've looked in various places and there is
information, but it never seems to start from scratch.
I am trying to move a record from the active worksheet to another worksheet
in the same workbook. Basically each record is three rows long. The record
may or may not, based on an indicator, need to be copied. I've have managed
to get that far, how I'm not sure. What has really stopped me is trying to
identify the range, which is dynamic, based on the active cell. From the
active cell I need to include 2 rows down and 16 columns to the right in a
range to copy to another worksheet in the same workbook. I really don't
understand how the naming a range process works in VBA. Is there anyone who
can assist with this?
My scary code is as follows:
Dim booBAR_FU_Y As Boolean
Dim booLAB_FU_Y As Boolean
Dim booNUR_FU_Y As Boolean
Dim booOE_FU_Y As Boolean
Dim booPHA_FU_Y As Boolean
Dim booRAD_FU_Y As Boolean
Dim booInd As Boolean
Dim M_Rec_Start_Cell As Variant
Dim M_Rec_End_Cell As Variant
Dim M_Index As Range
Dim Rec_Range As Range
Dim M_Rec_No_counter As Long
Dim BLN_Indicator As String
Dim Second_Indicator As Range
Dim FI_Counter As Integer
Dim r As Long
Dim z As Variant
'Dim BLN_Follow_Up As Range
' Dim OPR_Follow_Up As Range
Acct_Canc_Project.Sheet1.Activate
ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
Set M_Index = Range("A:A")
M_Index.Activate
ActiveCell.Offset(rowOffset:=5, columnOffset:=0).Activate
'Set M_Index = Range("A:A")
M_Rec_No_counter = 0
'Set Do loop to stop when three consecutive empty cells are reached.
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0)) And _
IsEmpty(ActiveCell.Offset(1, 0))
For Each m In ActiveCell
If m.Value Like "M*" Then
M_Rec_Start_Cell = ActiveCell.Address
ActiveCell.Offset(rowOffset:=2, columnOffset:=16).Select
M_Rec_End_Cell = ActiveWindow.RangeSelection.Address
Call Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
MsgBox ActiveWindow.RangeSelection.Address
Set Rec_Range = Range("Rangeaddress")
Rec_Range.Copy
'Trying to determine if this record needs to be copied to one of the
other areas
ActiveCell.Offset(0, 11).Activate
'This record will have three rows associated with it
For i = 1 To 3
If i = 2 Then
ActiveCell.Offset(1, 0).Activate
End If
If i = 3 Then
ActiveCell.Offset(1, 0).Activate
End If
BLN_Follow_Up = ActiveCell
'Checking if any of the L columns in the record are marked "y"
If BLN_Follow_Up = "y" _
Then
'i.value = 1 then it must be BAR that needs to follow up, 2=Lab
3=Nur
If i = 1 Then
booBAR_FU_Y = True
End If
If i = 2 Then
booLAB_FU_Y = True
End If
If i = 3 Then
booNUR_FU_Y = True
Else
'Error routine
End If
End If
Next
Sub Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
'
' Define_Rec_Range Macro
' Macro recorded 4/10/2006 by Shelagh Foley
'
'
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A6:P17").Select
ActiveWorkbook.Names.Add Name:="Rec_Range", RefersToR1C1:= _
"=Main!R6C1:R17C16"
Dim RangeAddress As String
RangeAddress = Names("Rec_Range").RefersTo
End Sub
Register To Reply
Bookmarks