I'm attempting something that should be very simple. However I've just started learning today and and can't quite understand.
This is my code so far:
Public Sub getCellData()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets(1)
Dim rng As Range: Set rng = ws.Range(Cells(1, 2), Cells(4, 2))
Debug.Print rng
End Sub
The data that I'm working with is this:
I keep getting the "Run-time error '13': Type mismatch" I googled the error and I'm still unsure of how to fix this. I want to print the variable rng in the immediate window.
Range
is an object, not a value. To output the values, you can iterate the Range
. Another way is to use the Transpose
function on a single row or column and then Join
to get a String
value of the array of values within the Range
.
Sample code:
Public Sub getCellData()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets(1)
' you need to prefix Cells with ws. to clarify the reference
Dim rng As Range: Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))
' you cannot debug print the object itself
'Debug.Print rng
' iterate the range
Dim rngCell As Range
For Each rngCell In rng
Debug.Print rngCell.Value
Next rngCell
' use the Transpose function for a single row or column
Dim strData As String
Dim wsf As WorksheetFunction: Set wsf = Application.WorksheetFunction
strData = Join(wsf.Transpose(rng.Value), ",")
Debug.Print strData
End Sub
Note I updated your Set rng = ...
to:
Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))
And added ws.
as a prefix to Cells
in order that the references were explicitly defined.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With