keeping range address in tact after changing range cell value

[jkicker]

This is my entire code. I am trying to make a list by moving my target cell (pastelocation). I thought I could use the range function and the .value function to move the pastelocation and paste the pertinent data, which works for the first iteration, but then when it comes to re-set the pastelocation cell, it can't because pastelocation has been changed to a 6 digit string instead of a range address. See highlighted line:

Dim hlevel As String, acct As String, aname As String, pname As String, pacct As String
    Dim cell As Range, cells As Range, pastelocation As Range
    
    [B]Set pastelocation = hws2.Range("A2")[/B]
    
    hws.Range("B6").Select
    
    Do Until ActiveCell.Value = "WW:Residence-TCard"
    
        If ActiveCell.Value Like "[0-9][0-9][0-9][0-9][0-9][0-9]" Then
            acct = ActiveCell.Value
            hlevel = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlNext).Value
            aname = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlPrevious).Value
            [B]pastelocation.Value = acct
            Set pastelocation = pastlocation.Offset(1, 0)[/B]
            pastelocation.Value = aname
            Set pastelocation = pastlocation.Offset(1, 0)
            pastelocation.Value = hlevel
            Set pastelocation = pastlocation.Offset(1, 0)
            
            If hlevel = -4 Then
            pacct = ActiveCell.Value
            pname = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlPrevious).Value
            End If
                        
            pastelocation.Value = pacct
            Set pastelocation = pastlocation.Offset(1, 0)
            pastelocation.Value = pname
            Set pastelocation = pastlocation.Offset(-4, 1)
            
        Else
            hws.UsedRange.Find("?*", ActiveCell, , , xlByRows).Select
        End If
        
                
    Loop

how do i just paste my variable data into my variable range address without changing the variable representing my range so that i can change my range variable properly afterwards?

 

[Z51]

First thing, you seem to have mistyped "pastelocation" multiple times as "pastlocation". Second, you don't need to re-Set the pastelocation, just reference it and offset it. Third, by using "pastelocation.Value = acct", you haven't assigned the acct value to the pastelocation variable, just the value of the current pastelocation cell. Any issue you're experiencing is probably because the code doesn't know what "pastlocation" is.

Dim hlevel As String, acct As String, aname As String, pname As String, pacct As String
    Dim cell As Range, cells As Range, pastelocation As Range
  
    Set pastelocation = hws2.Range("A2")
  
    hws.Range("B6").Select
  
    Do Until ActiveCell.Value = "WW:Residence-TCard"
  
        If ActiveCell.Value Like "[0-9][0-9][0-9][0-9][0-9][0-9]" Then
            acct = ActiveCell.Value
            hlevel = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlNext).Value
            aname = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlPrevious).Value
            pastelocation.Value = acct
            pastelocation.Offset(1, 0).Value = aname
            pastelocation.Offset(2, 0).Value = hlevel
          
            If hlevel = -4 Then
                pacct = ActiveCell.Value
                pname = ActiveCell.EntireRow.Find("?*", , , , xlByRows, xlPrevious).Value
            End If
                      
            pastelocation.Offset(3, 0).Value = pacct
            pastelocation.Offset(4, 0).Value = pname
            Set pastelocation = pastelocation.Offset(0, 1)
        Else
            hws.UsedRange.Find("?*", ActiveCell, , , xlByRows).Select
        End If

    Loop

 

[jkicker]

haha so it was just a typo then. oh that feels so good. thanks Z.

 

[Z51]

At the top of your code, before any Sub or Function calls, it often helps to add Option Explicit. This forces variables to be declared, so if you make a typo your code should fail to run until you fix the variable name.

You can even set that as a default in the VB Editor under Tools > Options > Require Variable Declaration. (Close and re-launch Excel for the change to take effect. After that, Option Explicit will be there by default.)

Option Explicit

Sub your_sub_name()
' Do stuff
End Sub