Is it possible to assign range to global variable

AndyJM87

New Member
Joined
Aug 31, 2018
Messages
10
Hi all,

I have 3 worksheets, one of these three is a master template. (sheet 3 for eg)

I need to collect data from the other two sheets (sheet 1 and sheet 2) and send to the relevant cells on the master template.

To do this I have assigned a set of global variables, hoping that I could set these variables to the range on either sheet 1 / sheet 2 and then using the variable name put the stored data onto sheet 3.

The worksheets ws… are all in the global module.

modGlobals

Code:
Public strSafety As String
Public strRefDtl As String
Public strDate As Date

Public rngTemp As Range
Public rngDTL As Range
Public dtlCell As Range


Public lngNtemp As Long
Public lngRTemp As Long
Public lngNdtl As Long
Public lngRdtl As Long
Public lngCell As Long
modUpdateTemplate

Code:
Option Explicit
Sub UpdateDRACAS()


    lngNdtl = 2
    lngRdtl = wsData.Range("B" & lngNdtl).End(xlDown).Row
    
    Set rngDTL = wsData.Range("B" & lngNdtl, "B" & lngRdtl)
    
    For Each dtlCell In rngDTL
        lngCell = dtlCell.Row
        
        With wsData
            strSafety = .Range("A" & lngCell).Value
            strRefDTL = .Range("B" & lngCell).Value
            strDte = .Range("C" & lngCell).Value
        End With
            
        With wsTemp
        
            lngNtemp = 1
            lngRTemp = wsTemp.Range("C" & Rows.Count).End(xlUp).Row + 1
            
            Call AssignConsts
            
            .Range("A" & lngRTemp) = strDte
            .Range("B" & lngRTemp) = strBy
            .Range("C" & lngRTemp) = strRefDTL
        End With
        
    Next
End Sub
Error: object variable or with block not set

Code with Error: lngRTemp = wsTemp.Range("C" & Rows.Count).End(xlUp).Row + 1
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,544
Office Version
365
Platform
Windows
Have you assigned a value to wsTemp?
 

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top