How store a range from excel into a Range variable

Question

I am reading some cells of excel using VBA   Function getData currentWorksheet as Worksheet  dataStartRow as Integer    dataEndRow as Integer  DataStartCol as Integer  dataEndCol as Integer       Dim dataTable as Range     dataTable   currentWorksheet Range currentWorksheet Cells dataStartRow        dataStartCol   currentWorksheet Cells dataEndRow  dataEndCol        getData   dataTable  EndFunction   It throws an error  object variable or with block variable not set  How take this range in a variable  Please guide me

User · Answer

What is currentWorksheet  It works if you use the built-in ActiveSheet   dataStartRow 1 dataStartCol 1 dataEndRow 4 dataEndCol 4 Set currentWorksheet ActiveSheet dataTable   currentWorksheet Range currentWorksheet Cells dataStartRow  dataStartCol   currentWorksheet Cells dataEndRow  dataEndCol

User · Answer

When you use a Range object  you cannot simply use the following syntax   Dim myRange as Range myRange   Range  A1       You must use the set keyword to assign Range objects   Function getData currentWorksheet As Worksheet  dataStartRow As Integer  dataEndRow As Integer  DataStartCol As Integer  dataEndCol As Integer       Dim dataTable As Range     Set dataTable   currentWorksheet Range currentWorksheet Cells dataStartRow  DataStartCol   currentWorksheet Cells dataEndRow  dataEndCol        Set getData   dataTable  End Function  Sub main       Dim test As Range      Set test   getData ActiveSheet  1  3  2  5      test select  End Sub   Note that every time a range is declared I use the Set keyword      You can also allow your getData function to return a Range object instead of a Variant although this is unrelated to the problem you are having

User · Answer

here is an example that allows for performing code on each line of the desired areas   pick either from top  amp  bottom of selection  of from selection   Sub doROWSb              WORKS    for do selected rows     SEE FIX ROWS ABOVE   small ver  Dim E7 As String     note   workcell E7 shows   BG381 E7   RANGE  E7       see eg below Dim r As Long        NOTE  this example has a paste formula s  down a column s    WILL REDUCE 10 HOUR DAYS OF PASTING COLUMNS  DOWN TO 3 MINUTES  Dim c As Long Dim rCell As RANGE  Dim LastRow As Long r   ActiveCell row c   ActiveCell Column    might not matter if your code affects whole line anyways  still leave as is  Dim FirstRow As Long     not in use  Delete if only want last row  note  this code already allows for selection as start Dim LastRow As Long   If 1 Then      if you are unable to delete rows not needed  just change 2 lines from  If 1  to if 0  to go from selection last row  to all rows down from selection  With Selection      FirstRow    Rows 1  row                  not used here  Delete if only want last row     LastRow    Rows  Rows Count  row         find last row in selection End With application CutCopyMode   False              if not doing any paste op below Else     LastRow   Cells Rows Count  1  End xlUp  row   find last row used in sheet End If application EnableEvents   True              EVENTS  need this  application ScreenUpdating   False           offset-cells row  col   RANGE E7  Select   TOP ROW SELECT RANGE  A1     vbNullString                   simple macros on-off switch  vb not here   If RANGE  A1   Value  gt  0 Then   For Each rCell In RANGE Cells r  c   Cells LastRow  c    new     rCell Select     make 3 macros for each paste macro below  your code here   If 1 Then      to if 0  if want to paste formulas formats all down a column     Selection EntireRow Calculate      calcs all selected rows  even if just selecting 1 cell in each row  might only be doing 1 row aat here  as part of loop  Else  dorows   DO ROWS    eg s for paste cells down a column  can make 3 separate macros for each  sub alte   altf  amp  altp       Selection PasteSpecial Paste  xlPasteFormulas  Operation  xlNone  SkipBlanks  False  Transpose  False     make sub alte       add thisworkbook   application OnKey    e     alte         Selection PasteSpecial Paste  xlPasteFormats  Operation  xlNone  SkipBlanks  False  Transpose  False      make sub altf       add thisworkbook   application OnKey    f     altf         Selection PasteSpecial Paste  xlPasteAll  Operation  xlNone  SkipBlanks  False  Transpose  False          amke sub altp       add thisworkbook   application OnKey    p     altp  End If Next rCell   application CutCopyMode   False             finished - stop copy mode  RANGE  A2   Select goBEEPS  2    0 25         beeps secs application EnableEvents   True              EVENTS   note   workcell E7 has  SUBSTITUTE SUBSTITUTE CELL  address   BG 369                  other col eg  shows  BG BG     SUBSTITUTE SUBSTITUTE CELL  address   BG2          ROW       amp     amp  SUBSTITUTE SUBSTITUTE CELL  address   BG2          ROW       End Sub    OTHER  Sub goBEEPSx b As Long  t As Double     beeps secs as   goBEEPS  2    0 25   OR   goBEEPS 2  0 25    Dim dt   as double     worked wo as double   Dim x   For b   b To 1 Step -1     Beep     x   Timer   Do   DoEvents   dt   Timer - x   If dt  lt  0 Then dt   dt   86400     86400 no  seconds in a day  in case hit midnight  amp  timer went down to 0   Loop Until dt  gt   t   Next End Sub

User · Answer

Declare your dim as a variant  and pull the data as you would from an array   i e    Dim y As Variant y   Range  A1 B2     Now your excel range is all 1 variable  array   y  To pull the data  call the array position in the range  A1 B2  or whatever you choose  e g    Msgbox y 1  1    This will return the top left box in the  A1 B2  range

User · Answer

Define what GetData is  At the moment it is not defined   Function getData currentWorksheet as Worksheet  dataStartRow as Integer  dataEndRow as Integer  DataStartCol as Integer  dataEndCol as Integer  as variant

[vba] How store a range from excel into a Range variable?

Examples related to vba

Examples related to excel