VBA for ThisWorkbook.Cells works with numbers only

[StuartWhi]

Hi Experts,

I have converted some different code found on the site to do what I want but have two small issues I hope someone can assist me with.

Basically what I want to do is find a blank set of cells within a column between to values then copy the top value to all cells (then continue down the column).

The two issues,

How to change this to the ActiveSheet not a fixed sheet…

Set rng = ThisWorkbook.Worksheets(2).UsedRange

How to change this to allow for a combination of Numbers and Letters (at the moment it only works for numbers (courses an “run-time error ‘13’: Type mismatch) if letters are in the cell).

Factor = rng.Cells(StartRow, ColNo).Text

Full code below.

Option ExplicitSub CopyDown()


Dim rng As Range, RowNo As Long, Recs As Long, ColNo As Variant
Dim StartRow As Long, EndRow As Long, Factor As Double


MsgBox ("Please back-up before selecting ""Ok"" impossible to recover after you continue.")


Set rng = ThisWorkbook.Worksheets(2).UsedRange
ColNo = Application.InputBox("Enter required column letter.", "Column Input", Type:=2)  ' Not accepting letters
              
StartRow = FindNoEmpty(rng, 1, ColNo)
EndRow = FindNoEmpty(rng, StartRow + 1, ColNo)


Do While (StartRow <= rng.Rows.Count) And (EndRow <> 0)
    Recs = EndRow - StartRow
    Factor = rng.Cells(StartRow, ColNo).Text


        For RowNo = StartRow + 1 To EndRow - 1
            rng.Cells(RowNo, ColNo).Value = Factor
        Next RowNo
        
        StartRow = EndRow
        EndRow = FindNoEmpty(rng, StartRow + 1, ColNo)
Loop
MsgBox "Complete, hope it worked.  Re-run for different columns", vbInformation


End Sub
Function FindNoEmpty(rng As Range, ByVal StartRowNo As Long, ByVal Col As Variant) As Long
Dim RowNo As Long
    
If StartRowNo <= 0 Then
    Exit Function
End If
    
For RowNo = StartRowNo To rng.Rows.Count
    If Not IsEmpty(rng.Cells(RowNo, Col)) Then
        FindNoEmpty = RowNo
        Exit Function
    End If
Next RowNo
        
End Function

Thanks in advance.

Stuart.

 

[VoG]

You can try

Set Rng = ActiveSheet.UsedRange

Also,Dim Factor as Variant instead of Double

 

[StuartWhi]

Hi Peter,

The Variant killed me (had tried the ActiveSheet.UsedRange...), as always great answers by great people.

Catch you next time.

Stuart.