Pass one step of code to the next

[julhs]

As I had no replies to my initial thread and have made so many alterations/additions to it; I have decided that it’s better to start a new one and include a Xl2bb
Linked to other thread
Combining 2 separate codes into one

The ultimate problem is failure to pass; “ UsedRng = Range("s" & firstRow & ":s" & lastRow).Address” '= "$s$10:$S$22" to the next step of the code.
Please bear in mind that code I’ve posted is my ham-fisted way to test returned results/outcomes, a lot of which will be removed/ rationalised in my final draft.

This is the section of code where I have not got things right!!

UsedRng = Range("s" & firstRow & ":s" & lastRow).Address '= "$s$10:$S$22"
'End With 'temp subbed out, not sure if should be here or at the very bottom
'#########################################################
'?????????????????????????????????????????????????????
' This is where I'm failing to pass the "ACTUAL USED RANGE" ("$S$10:$S$22") from the above section to
If UsedRng Is Nothing Then Exit Sub
If copyCells Is Nothing Then Exit Sub

For Each r In rng
r.Interior.Color = r.DisplayFormat.Interior.Color ' This converts the Conditional Formatting effect to a "Fixed" static color formatt
Next r
rng.FormatConditions.Delete 'This deletes the CF rule from the range just converted to "Fixed" colors before it copies it
'---------------------------------------------------
Set rng = sht.Range("t:t").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
Lrow = sht.Cells(sht.Rows.Count, "T").End(xlUp).Row
Lrow = rng.Row
DestRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row 'Locates LAST row of Input Section to paste copied cells to
For i = 9 To Lrow ' Is the start row to copy FROM
Set rng = sht.Range("S" & firstRow & i & ":S" & lastRow & i) ' Col that looking for "Comments" in
If Not rng.Comment Is Nothing Then
'?????????????????????????????????????????????????????
'#########################################################
Code then continues …..

Complete code and Xl2bb

' #### Some of the DIM's below may NOW be redundant
Public Sub juhls4_plus2Combined_A4()
Dim sht As Worksheet
Dim lastcell As Range, firstcell As Range
Dim lastRow As Long, firstRow As Long
Dim findString As String
Dim LstUsedRow As Variant
Dim FstUsedCell  As Variant

Dim Lrow As Long, DestRow As Long, i As Integer
Dim Frow As Long
Dim rng As Range, r As Range
Dim LrwD As Long
Dim CFcell As Range
Dim copyCells As Range
Dim Usedcells As Long
Dim UsedRng As Range    ' use for "ACTUAL USED RANGE" of Col S found
Dim ClearRng As Range   ' use for " WorksheetFunction.CountA( "
          
       ' Application.ScreenUpdating = False  ' re-apply when testing complete
    Set sht = ThisWorkbook.ActiveSheet
'----------------------------------------------------------
                   ' Test "S7" for CF rules
    Set CFcell = sht.Range("S7")  'cell with CF rules for copying
      If CFcell.FormatConditions.Count = 0 Then
         MsgBox CFcell.Address & "  NO CF rules in Cell"
          ' Exit Sub   ~~~~~~~  Maybe add code to copy CF rules FROM S6 INTO S7 and then continue ??
        Else
         MsgBox CFcell.Address & "  Cell contains CF rules"
      End If
'----------------------------------------------------------
        Frow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
        LrwD = sht.Cells(sht.Rows.Count, "AN").End(xlUp).Row
        
   Set ClearRng = sht.Range("AL" & Frow + 1 & ":AN" & LrwD + 1)
       If WorksheetFunction.CountA(ClearRng) = 0 Then
         MsgBox "  Range Is Empty"
        GoTo line1:
      Else
        MsgBox "  Range NOT Empty"
   End If
'-----------------------------------------------------
            ' This is to clear the Destination range before a new paste,it first_
            '   finds the row beneath target row with "Cash Paid" in column T
        Frow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
         LrwD = sht.Cells(sht.Rows.Count, "AN").End(xlUp).Row
         sht.Range("AL" & Frow + 1 & ":AN" & LrwD + 1).Clear
line1:  On Error Resume Next   ' Is line code goes to if "ClearRng" ("AL25:AN30") is "EMPTY/BLANK"
'---------------------------------------------------
        ' This section returns the "ACTUAL USED RANGE" of column S ("S10:s22")-(Curtesy of Alex Blakenburg)
     With sht
             ' Get start of Data Range
          findString = "Bank & Cash"
     Set firstcell = .Range("S:S").Find(what:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If firstcell Is Nothing Then Exit Sub
       
          firstRow = firstcell.End(xlDown).Row
       
             ' Get end of Data Range
         findString = "Cash Paid"
           Set lastcell = .Range("T:T").Find(what:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If lastcell Is Nothing Then Exit Sub
       
     Set lastcell = lastcell.Offset(, -1)        ' Move across to amount column
        If lastcell.Offset(-1) <> "" Then
            Set lastcell = lastcell.Offset(-1)
        Else
            Set lastcell = lastcell.End(xlUp)
       End If
        lastRow = lastcell.Row
    
    Range("AM24") = Range("s" & firstRow & ":s" & lastRow).Address  ' Puts ACTUAL USED RANGE" into a cell on sheet
    UsedRng = Range("s" & firstRow & ":s" & lastRow).Address  '= "$s$10:$S$22"
        ' End With   ' temp subed out, not sure if should be here or at the very bottom

'#########################################################
'?????????????????????????????????????????????????????
       '  This is where I'm failing to pass the "ACTUAL USED RANGE" ("$S$10:$S$22") from the above section to
   If UsedRng Is Nothing Then Exit Sub
   If copyCells Is Nothing Then Exit Sub ' this needs to be changed to..... ??

    For Each r In rng
        r.Interior.Color = r.DisplayFormat.Interior.Color ' This converts the Conditional Formatting effect to a "Fixed" static color formatt
    Next r
        rng.FormatConditions.Delete    ' This deletes the CF rule from the range just converted to "Fixed" colors before range is copied
 '---------------------------------------------------
 
   Set rng = sht.Range("t:t").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
        Lrow = sht.Cells(sht.Rows.Count, "T").End(xlUp).Row
        Lrow = rng.Row
                
        DestRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row ' Locates LAST row of Input Section to paste copied cells to
      For i = 9 To Lrow                                        ' Is the start row to copy FROM
      Set rng = sht.Range("S" & firstRow & i & ":S" & lastRow & i) ' Col that looking for "Comments" in
    If Not rng.Comment Is Nothing Then
'?????????????????????????????????????????????????????
'#########################################################

        sht.Range("P" & i).Copy                             ' Corresponding Inv#
        sht.Range("AL" & DestRow + 1).PasteSpecial xlPasteAll
        sht.Range("R" & i & ":S" & i).Copy     ' Payment details List & Bank Col
        sht.Range("AM" & DestRow + 1 & ":AN" & DestRow + 1).PasteSpecial xlPasteAll ' Destination for copied cell
    
          DestRow = DestRow + 1
     End If
       Next
        copyCells.Copy  ' This needs to relate UsedRng "$S$10:$S$22", maybe "UsedRng.Copy"
            pasteRng.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
  
  sht.Range("Am" & Frow).Select  ' Makes cell at top of Destination range ("AM24")the active cell
            'Application.CutCopyMode = False  ' Dont need this as is for when using InputBoxes
        ' Application.ScreenUpdating = True    ' re-apply when testing complete
        
   End With   ' not sure if should be here or at end of "ACTUAL USED RANGE" section
'======================================================
        ' For testing purposes
    Debug.Print "UsedRng: " & Range("S" & firstRow & ":S" & lastRow).Address 'this gives $S$10:$S$22
    Debug.Print "UsedRng: " & Range("S" & firstRow & i & ":S" & lastRow & i)
    Debug.Print UsedRng
    Debug.Print "ClearRng: " & Range("AL" & Frow + 1 & ":AN" & LrwD).Address 'this gives  $AL$25:$AN$30
    Debug.Print "rng: " & Range("S" & firstRow & ":S" & lastRow).Address    'this gives rng: $S$10:$S$22
    Debug.Print copyCells.Copy
    Debug.Print CFcell.Copy
    Debug.Print "CFcell: " & CFcell.Address
    Debug.Print Range("s" & firstRow & ":s" & lastRow).Address
    Debug.Print firstcell.Address
    Debug.Print lastcell
    Debug.Print lastcell.Address
     
  End Sub

Accounts Code TestingBook1.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
4
5
6		Date	Inv #	Payment Method	Payment Details List	Bank & Cash		Drawings	Purchases of Stock / Materials	Tool,Weather/Safety equip	Repairs & Renewals	Motor Expenses	Hire Charges	Liability Insurance	N.I cont / TGWU	Gen Insur Office Postage/ Stationary	Misc	Acquisition of Assets	Let Property	Bank Charges	Utilities / House	Income Tax
7	s	Leave these 2 row empty
8	s
9	s
10	s	Apr-07	gs002	St Order	Rent	694.50															694.50
11	s	Apr-07		D. Debit	Amazon Prime	5.99		5.99
12	s	Apr-07	gs005	My Sol	Heating Oil	540.22															540.22
13	s	Apr-08		D. Debit	EE Mobile(1)	28.21											28.21
14	s	Apr-08		D. Debit	AA membership	39.61					39.61
15	s	Apr-11	gs010	My Sol	Fuel Other	127.00						127.00
16	s	Apr-12	gs015	My Sol	Tesco	29.44		29.44
17	s	Apr-12	gs020	My Sol	Tesco	113.55		113.55
18	s	Apr-13	gs023	My Sol	Amazon Prime	2.99		2.99
19	s	Apr-19		D. Debit	EDF energy	16.25															16.25
20	s	Apr-19	gs025	My Sol	Tesco	15.00		15.00
21	s	Apr-13	gs030	My Sol	Amazon Prime	5.99		5.99
22	s	Apr-19		D. Debit	EDF energy	21.00															16.25					A4 Used Range is:-
23	s
24	s					1639.75	Cash Paid	172.96			39.61	127.00					28.21				1267.22
25	s																								gs002	Rent	694.50
26	s				There is data in this area but NOT relevant																					EE Mobile(1)	28.21
27	s																									Fuel Other	127.00
28	s																		This is just for convenience, used for copying/pasting back into AL25:AN30 during testing						gs010	EDF energy	16.25
29	s																									Amazon Prime	5.99
30																					A4 Used Range is:-				gs030	EDF energy	21.00
31
32																					$S$10:$S$22
33
34																				gs002	Rent	694.50
35																					EE Mobile(1)	28.21
36																					Fuel Other	127.00
37																				gs010	EDF energy	16.25
38																					Amazon Prime	5.99
39																				gs030	EDF energy	21.00
40
41
April 22 - 23 (minimal)

Cell Formulas
Range	Formula
S24,U24:AI24	S24	=SUM(S$7:OFFSET(S24,-1,0))
AJ24	AJ24	=SUM(AJ$7:OFFSET(AI24,-1,0))
T25	T25	=SUMIF(Q$7:$Q23,$T24,$S$7:$S23)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S7	Expression	=$Q7="My Sol"	text	YES
S7	Expression	=$Q7="D. Debit"	text	YES
S7	Expression	=$Q7="St Order"	text	YES
S6	Expression	=$Q6="My Sol"	text	YES
S6	Expression	=$Q6="D. Debit"	text	YES
S6	Expression	=$Q6="St Order"	text	YES
S10:S22	Expression	=$Q10="My Sol"	text	YES
S10:S22	Expression	=$Q10="D. Debit"	text	YES
S10:S22	Expression	=$Q10="St Order"	text	YES
S23	Expression	=$Q23="My Sol"	text	YES
S23	Expression	=$Q23="D. Debit"	text	YES
S23	Expression	=$Q23="St Order"	text	YES

 

[julhs]

PS edit;
Just reread what you said, so are you suggesting that if I declare “UsedRng” as a String and not as Range (as currantly doing )
I wouldn’t be facing some issues?

 

[MARK858]

So what should I use instead of “On Error Resume Next” in this situation?
On the basics that using "On Error GoTo 0" instead, just bugs out on a later line?

It is not what to use instead, you can use the

On Error Resume Next

to resolve a specific expected error but you end it with

On Error GoTo 0

immediately after those specific error lines have passed.

On Error Resume Next if not ended will hide all future errors in the code and so you if you leave it "open" it could stop the parts of your code functioning as you aren't seeing the errors

Any future errors you assess separately and either code around (ideally if possible) or use another error handler.

Glancing at your code it looks like you should have ended it after the following block of "Finds" and then possibly used another On End Resume Next on the last block of "Finds"

 

[MARK858]

PS edit;
Just reread what you said, so are you suggesting that if I declare “UsedRng” as a String and not as Range (as currantly doing )
I wouldn’t be facing some issues?

No I am saying that in your original code UsedRng was a string as you were using .Address (you found this out in post 3 with your "compile" error) not a Range

As a demo WhatWasInPost1 macro is the equivalent of what you had...

Sub WhatWasInPost1()
    Dim myRng As String          'used string rather than range as it won't get passed the next line otherwise
    myRng = Range("A1:A10").Address

    If Not myRng Is Nothing Then  'you should get a type mismatch here because of the .Address
        MsgBox "yes"
    Else
        MsgBox "no"
    End If
End Sub

Sub AlternativeCode()
    Dim myRng As Range
    Set myRng = Range("A1:A10")
    
    If Not myRng Is Nothing Then
        MsgBox "yes"
    Else
        MsgBox "no"
    End If
End Sub

The

If Not myRng Is Nothing Then

line requires an Object (in this case a range) not a string as it tests if an Object has been assigned to an object variable

Btw what do you think you are testing with the

If UsedRng Is Nothing Then Exit Sub

line?

 

[julhs]

Post#12
Thank you Mark that was REALLY helpful.
I quickly read your post#13, but have to go out, will respond tomorrow.
Thanks for help.
julhs

 

[HaHoBe]

Hi julhs,

there's quite an interesting comment at the beginning of the code you posted:

' #### Some of the DIM's below may NOW be redundant

Really?

'/// Make sure that you only have one variable for the same item
'/// For example Frow, lastCell, DestRow all are used for a Find of "Cash & Paid" in Column T
'///    plus a range is set to that cell.
'/// I would just set a range to the cell as you may get the row by using rng.Row and saving
'///    at least 3 codelines dealing with the same thing plus avoiding three Variables.
'///    And this should be done pretty soon after setting an object to the worksheet.
'/// I mentioned rng: when you first try to loop through that range object it hasn't been set.
'/// 'On Error Resume Next' just goes on and ignores the error.

'/// There are two codelines directly beneath each other in which you assign a row number to Lrow.
'/// Only the second one (which is the wrong value as far as I can spot) is used.

  If WorksheetFunction.CountA(ClearRng) = 0 Then
    MsgBox "  Range Is Empty"
  Else
    MsgBox "  Range NOT Empty"
    '/// moved to avoid command 'On Error Resume Next'
    '-----------------------------------------------------
    ' This is to clear the Destination range before a new paste,it first_
    '   finds the row beneath target row with "Cash Paid" in column T
    Frow = ws.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
    LrwD = ws.Cells(ws.Rows.Count, "AN").End(xlUp).Row
    ws.Range("AL" & Frow + 1 & ":AN" & LrwD + 1).Clear
  End If

Holger

 

[julhs]

To both Mark & Holger
I see and hear what you are both saying and vaguely following it??
Proof will be when I come to apply it!
I’ve come to the conclusion that there is more for me to address than it being a simple fix??
Certainly removing the On Error Resume Next has helped me with identifying where other problems lie.

Mark; yes of course you were right, it bugged out on “UsedRng”.

Holger; does having variables with different names BUT referring to the same item cause serious code problems (rather than it just being unnecessary and untidy code)

At moment I’m blundering my way through attempting to address issues!!

 

[HaHoBe]

Hi julhs,

to be honest: no. But if you want help from anybody from the outside that certainly will be one aspect to be mentioned whenever somebody looks at the code.

One thing I didn't follow myself for a long time is use names for the variables which express what they are used for (they need not to have a prefix but that helps me to identify which type they are and how to pass values or objexts to them). You have used Frow better suited might be rowCashPaid as that would express the row holding the expression "Cash Paid". If you take lastCell you would soon have found out that the name of the variable does not fit what you use it for as it is the row in which "Cash Paid" is found which might be rowCashPaid. Using Option Explicit should point at that when you run Debug after saving the workbook.

Another aspect may be the use of variables. I have come to the point to use any variable just for one single object (and not re-use it). Example: you use findString and assign first "Bank & Cash" and shortly after that "Cash Paid". I would recommend to use constants for these items and have them placed after the dims

Dim ClearRng As Range   ' use for " WorksheetFunction.CountA( "

Const cstrBANK As String = "Bank & Cash"
Const cstrPAID As String = "Cash Paid"
          
       ' Application.ScreenUpdating = False  ' re-apply when testing complete

Another example of how to avoid variables with your original code posted

        ' This section returns the "ACTUAL USED RANGE" of column S ("S10:s22")-(Curtesy of Alex Blakenburg)
     With sht
             ' Get start of Data Range
          findString = "Bank & Cash"
     Set firstcell = .Range("S:S").Find(What:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If firstcell Is Nothing Then Exit Sub
       
          firstRow = firstcell.End(xlDown).Row
       
             ' Get end of Data Range
         findString = "Cash Paid"
           Set lastcell = .Range("T:T").Find(What:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If lastcell Is Nothing Then Exit Sub
       
     Set lastcell = lastcell.Offset(, -1)        ' Move across to amount column
        If lastcell.Offset(-1) <> "" Then
            Set lastcell = lastcell.Offset(-1)
        Else
            Set lastcell = lastcell.End(xlUp)
       End If
        lastRow = lastcell.Row
    
    Range("AM24") = Range("s" & firstRow & ":s" & lastRow).Address  ' Puts ACTUAL USED RANGE" into a cell on sheet
    UsedRng = Range("s" & firstRow & ":s" & lastRow).Address  '= "$s$10:$S$22"

maybe written as

  With ws
    ' Get start of Data Range
    Set rngFirstCell = .Range("S:S").Find(What:=cstrBANK, LookIn:=xlValues, LookAt:=xlWhole)
    If rngFirstCell Is Nothing Then Exit Sub
    
    ' Get end of Data Range
    Set rngLastCell = .Range("T:T").Find(What:=cstrPAID, LookIn:=xlValues, LookAt:=xlWhole)
    If rngLastCell Is Nothing Then Exit Sub
    
    Set rngLastCell = rngLastCell.Offset(, -1)        ' Move across to amount column
    If rngLastCell.Offset(-1) <> "" Then
      Set rngLastCell = rngLastCell.Offset(-1)
    Else
      Set rngLastCell = rngLastCell.End(xlUp)
    End If
 
    Range("AM24") = .Range(rngFirstCell, rngLastCell).Address(0, 0) ' Puts ACTUAL USED RANGE" into a cell on sheet
    Set UsedRng = .Range(rngFirstCell, rngLastCell)  '= "s10:S22"
     ' End With   ' temp subed out, not sure if should be here or at the very bottom

Ciao,
Holger