jBenzer
07-16-2019, 12:34 PM
Hi,
I am pretty new to VBA and I have been trying to execute the following code however, it is giving me an error "Application-defined or object-defined error". I am unable to figure out where am I going wrong.
Can someone please review my code and help me fix it? Thank you very much in advance :)
Sub SelectBetween()
Dim sourceRange As Range
Dim transposeRange As Range
Dim rowCount As Integer
Dim rangeCount As Long
'Initialize rowCount'
rowCount = 0
Worksheets("sheet1").Activate
'Determine max number for loop'
Set sourceRange = Range(Range("A:A").Find("abc"), Range("A:A").Find("xyz"))
rangeCount = sourceRange.Rows.Count
Debug.Print rangeCount
For i = 1 To rangeCount
Set sourceRange = Range(Range("A:A").Find("FROM"), Range("A:A").Find("RXNBR"))
'sourceRange.Select'
'Copy range to transpose'
sourceRange.Copy
'Transpose the selected range'
Set transposeRange = ActiveSheet.Cells(rowCount + 1, "B")
transposeRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
sourceRange.Delete
rowCount = rowCount + 1
Next i
'Delete empty column A'
Columns(1).EntireColumn.Delete
End Sub
I am pretty new to VBA and I have been trying to execute the following code however, it is giving me an error "Application-defined or object-defined error". I am unable to figure out where am I going wrong.
Can someone please review my code and help me fix it? Thank you very much in advance :)
Sub SelectBetween()
Dim sourceRange As Range
Dim transposeRange As Range
Dim rowCount As Integer
Dim rangeCount As Long
'Initialize rowCount'
rowCount = 0
Worksheets("sheet1").Activate
'Determine max number for loop'
Set sourceRange = Range(Range("A:A").Find("abc"), Range("A:A").Find("xyz"))
rangeCount = sourceRange.Rows.Count
Debug.Print rangeCount
For i = 1 To rangeCount
Set sourceRange = Range(Range("A:A").Find("FROM"), Range("A:A").Find("RXNBR"))
'sourceRange.Select'
'Copy range to transpose'
sourceRange.Copy
'Transpose the selected range'
Set transposeRange = ActiveSheet.Cells(rowCount + 1, "B")
transposeRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
sourceRange.Delete
rowCount = rowCount + 1
Next i
'Delete empty column A'
Columns(1).EntireColumn.Delete
End Sub