Crimpy Crimpy - 1 month ago 6
HTML Question

Excel range loop with variable in name

I have a function that will parse text and cell values to make code for an HTML table.

Here's my code:

Function Html(Rng1 As Range, Rng2 As Range, Head As Boolean) As String
' this function will take columns of data and properly format them for html table
Dim retVal As String
Dim i, j, p As Integer
p = 1
'if ranges doesn't contains one column and same rows count - return #VALUE error
If Rng1.Rows.count <> Rng2.Rows.count Or _
Rng1.Columns.count <> 1 Or Rng2.Columns.count <> 1 Then
Html = CVErr(xlErrValue)
Exit Function
End If

retVal = ""

For j = 1 To 2

If Head = True Then
retVal = retVal & "<th>" & Rng1.Cells(1, j)& "</th>"
If j = 2 Then
retVal = retVal & "<th>" & Rng2.Cells(1, j) & "</th>"
End If
p = 2
End If

retVal = retVal & "<tr>"

For i = p To Rng1.Rows.count
retVal = retVal & "<td>" & Rng & j & .Cells(i, 1) & "</td>"

Next i

retVal = retVal & "</tr>"
Next j


Html = "<table>" & retVal & "</table>"
End Function


The problem is I want to cycle through the two ranges. The ranges are called
Rng1
and
Rng2
. I want to combine :Rng" and the loop identifier
j
to make
Rng1.Cells(i,1)
but since it's in the middle of that object I'm not sure how to combine it.

Any help on this?

Answer

I think I see what you are trying to do now. You can create an array of range objects:

dim rangeArray
set rangeArray = array(Rng1, Rng2)

and use it like this:

retVal = retVal & "<td>" & rangeArray(j).Cells(i, 1) & "</td>"