VBA code running but not having effect

Question

The below code was working for the intended purpose: copying some rows from one worksheet to another. But, when I added the parts with rangeToDelete, which is meant to clear out the space I'm copying into before each new running of the code. I'm not sure what's happening, but when I use MsgBox to validate some parts of the code, it looks like it's the loop that isn't functioning. But, without kicking up an error, I don't know why.

Option Explicit

Sub findCells()
  Dim topCell As String
  Dim leftCell As String
  Dim refCell As Range
  Dim sht As Worksheet
  Dim lastRow As Long
  Dim i As Long
  Dim cellVal As String
  Dim altCounter As Long
  Dim crange As Range
  Dim rangeToDelete As Range

  Set rangeToDelete = Worksheets("Summary").Cells(31, "A").CurrentRegion
  rangeToDelete.Value = ""

  Set refCell = ActiveCell
  topCell = refCell.End(xlUp).Value
  leftCell = refCell.End(xlToLeft).Value

  Set sht = Worksheets(topCell)
  lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
  With sht
    .Range("A1:G1").Copy Worksheets("Summary").Range("A31:G31")
    altCounter = 31
    For i = 1 To lastRow
      cellVal = Cells(i, 5).Value
      If leftCell = cellVal Then
        altCounter = altCounter + 1
        Set crange = .Range("A" & i & ":" & "G" & i)
        crange.Copy Worksheets("Summary").Range("A" & altCounter & ":" & "G" & altCounter)
      End If
    Next i
  End With

End Sub

Don't use .Value ="" but rather .ClearContents. You're filling those cells with an empty string, not clearing them. — jkpieterse
– jkpieterse, Commented Feb 14, 2017 at 15:42
Try seeing if adding a dot helps here cellVal = .Cells(i, 5).Value. — SJR
– SJR, Commented Feb 14, 2017 at 16:04
I made all the suggested changes. It was @SJR 's suggestions that finally fixed it. — VBAnking
– VBAnking, Commented Feb 14, 2017 at 16:31

Deb · Accepted Answer · 2017-02-14 16:05:56Z

0

range.cells used numbers not text use 1 for column A in your code. and also use to clear the contents method as suggested.

answered Feb 14, 2017 at 16:05

Deb

1311 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jordan Over a year ago

.Cells does accept alphabetical column references as well as numerical so .Cells(1, 1) and .Cells(1, "A") are both correct.

Collectives™ on Stack Overflow

VBA code running but not having effect

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related