PDA

View Full Version : elements of array



idnoidno
05-22-2017, 11:57 PM
Sub ta()
Dim mar
Dim i As Integer, n As Integer
Dim rg As Range
Dim sh
Worksheets.Add.Name = "sheet2"
Worksheets("sheet1").Range("a1:s2").Copy
Worksheets("sheet2").Rows(1).Select
Worksheets("sheet2").Paste
With Worksheets("sheet1")
Set rg = .Range("a1").CurrentRegion
mar = rg
n = 3
For i = 3 To UBound(mar)
If (mar(i, 3) > mar(i, 6) And mar(i, 7) * 0.8 > mar(i, 5)) Then
.Rows(i).Copy
Worksheets("sheet2").Select
Rows(n).Select
Worksheets("sheet2").Paste
n = n + 1
End If
Next i
End With
End Sub

Why some elements of array format is string, some double?

snb
05-23-2017, 12:26 AM
Please do not post unreadable pictures. Post a sample file instead.

mdmackillop
05-23-2017, 05:23 AM
If you feed in mixed formats you will get mixed formats

Sub test()
Dim mar, mar1
mar = Array(1, "two", 3.05, 3.11111111111111)
Cells(1, 1).Resize(, 4) = mar
mar1 = Cells(1, 1).Resize(, 4)
Cells(3, 1).Resize(, 4) = mar1
End Sub

Paul_Hossler
05-23-2017, 07:11 AM
mar is Dim-ed as a Variant (as is sh)

rg is a 2D range of Variant (since the cells can hold anything, strings, doubles, etc.)

So

mar = rg


makes mar a 2D array of Variant




Dim mar
Dim rg As Range
Dim sh
Worksheets.Add.Name = "sheet2"
Worksheets("sheet1").Range("a1:s2").Copy
Worksheets("sheet2").Rows(1).Select
Worksheets("sheet2").Paste
With Worksheets("sheet1")
Set rg = .Range("a1").CurrentRegion
mar = rg

snb
05-23-2017, 07:34 AM
Sub M_snb()
Sheets.Add.Name = "sheet2"
Sheets("sheet1").Range("a1:s2").Copy Sheets("sheet2").Cells(1)

With Sheets("sheet1")
Set rg = .Cells(1).CurrentRegion
mar = .Cells(1).CurrentRegion
End With

MsgBox TypeName(rg)
MsgBox TypeName(mar)
End Sub