next without for error, used to work

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
280
wrong thread title .. should be error 91 .. Cannot work out how to edit title or delete thread

I have a working macro , which I copied and modified, took some lines out , added some lines, worked well until I added "
gstValue = ActiveCell.Offset(1, 4).address
e = ActiveCell.Offset(1, 3).Address
d = ActiveCell.Offset(1, 2).Address "
for the life of me I can't sort out the " Run time error 91 - object variable or block variable not set", Ive tried using "set" then I get type mismatch.
can anyone give me a tip to resolve this please
Code:
Private Sub CommandButton1_Click()
'sub try this() by Jolivanes
Dim c As Range
Dim datecheck As Date
Dim WorkingDate As Range
Dim gstValue As Range
Dim d As Range
Dim e As Range
datecheck = Range("'report entry'!a8").Value

For Each c In Range("'register report 19_20'!b1:b2000")
    ' need to change 'register report 2018_2019' to match sheet name as per current financial year to suit
  Sheets("register report 19_20").Activate
    ' need to change 'register report 2018_2019' to match sheet name as per current financial year to suit
    If c.Value = datecheck Then
       
     
            ' this transfers cells in "report entry" sheet to register report sheet
  Range(c.Address).Select
            ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown 'insert row
        ActiveCell.Offset(1, 0).Value = datecheck ' payment date                         "B"
        ActiveCell.Offset(1, -1).Value = Range("'report entry'!d4").Value ' invoice no   "A"
        ActiveCell.Offset(1, 4).Value = Range("'report entry'!f2").Value ' gst           "F"
        ActiveCell.Offset(1, 1).Value = Range("'report entry'!h2").Value ' net sales     "C"
        ActiveCell.Offset(1, 22).Value = Range("'report entry'!p2").Value ' customer name "X"
       
       Set gstValue = ActiveCell.Offset(1, 4).Range
        e = ActiveCell.Offset(1, 3).Address
        d = ActiveCell.Offset(1, 2).Address
       
                e.Value = gst.Value * 9 + gst.Value
             d.Value = ActiveCell.Offset(1, 1).Value - e.Value
      
          '     ActiveCell.Offset(0, -1).Value = Range("'report entry'!d2").Value ' change -1 to correct columm with respect
          '                               to date column in register report sheet (-1 = date -1 , 3 = date +3)
          '                                 change d2 to match corerct column in report entry sheet
        Range("'report entry'!a2:q2").Value = ""     ' empties report entry to blank cells
        Range("'report entry'!g4").Value = ""        ' empties customer name to blank cells
             ActiveCell.Offset(1, -1).Select        'colours report no to no colour to help identify invoice from report
                With Selection.Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                 End With
     
           
             Exit Sub
        End If
        Next c
     'end sub
        ' save work in "sample invoice test.xlsm"
        Workbooks("2019_2020 supplier invoices.xlsm").Activate ' MUST CHANGE TO CORRECT WORKBOOK
            ActiveWorkbook.Save
End Sub
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,989
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
Set gstValue = ActiveCell.Offset(1, 4)
Set e = ActiveCell.Offset(1, 3)
Set d = ActiveCell.Offset(1, 2)
 

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
280
Thanks Akuini, worked a charm, I had tried "set gstValue .... " with the ".address" and it didn't work, I also tried without the "set" and without the ".address" and it didn't work. so I needed both the "set" and not the ".address"
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,989
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,745
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top