crmpicco
05-26-2005, 02:28 AM
Can anyone see away of making this code more dynamic with respect to the 'TotalCell' variable:
While flag <> True
sRightRange = getColumnLetter(iCol + iFlag) & iRow
'... the amount of cells that are merged together
'... a.k.a the Column Span for the HTML table
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell2 = TotalCell
'... if it is not empty then write another <td>
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
'... the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "</td>"
sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell3 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell4 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell5 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell6 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell7 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell8 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell7 + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow
' ...TotalCell = Range(sRightRange).MergeArea.Cells.Count
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell8 + TotalCell7 + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow
' ...TotalCell = Range(sRightRange).MergeArea.Cells.Count
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
End If
iFlag = iFlag + 1
'... while flag <> true
Wend
While flag <> True
sRightRange = getColumnLetter(iCol + iFlag) & iRow
'... the amount of cells that are merged together
'... a.k.a the Column Span for the HTML table
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell2 = TotalCell
'... if it is not empty then write another <td>
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
'... the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "</td>"
sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell3 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell4 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell5 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell6 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell7 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow ' ...
TotalCell = Range(sRightRange).MergeArea.Cells.Count
TotalCell8 = TotalCell
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell7 + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow
' ...TotalCell = Range(sRightRange).MergeArea.Cells.Count
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
sRightRange = getColumnLetter(iCol + TotalCell8 + TotalCell7 + TotalCell6 + TotalCell5 + TotalCell4 + TotalCell3 + TotalCell2 + TotalCell + iFlag) & iRow
' ...TotalCell = Range(sRightRange).MergeArea.Cells.Count
If Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "<td colspan=" & TotalCell & " align=center>"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(sRightRange).Text))
the_Content = the_Content & "</td>"
'sRightRange = getColumnLetter(iCol + TotalCell + iFlag) & iRow
ElseIf Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "</tr>"
flag = True
'... if the cell is empty
End If
End If
iFlag = iFlag + 1
'... while flag <> true
Wend