For Next nested loop

[cdhoughton]

I have some code that requires slight adjustment:

I have a table, C columns wide by L rows. I want code that looks in each cell, checks if it is bold, writes some text in a target cell if it is "bold" or "not bold", then checks the same cell if it is italic, then notes that one down from the previous target cell, and so on.

I need to go through each column in row 1, then each column in row 2 until row L. If anyone can help with the code below, or provide a simpler example I can adapt, I'd be much appreciated!!

PS I added the CellAddress to identify where the for next loop was looking, and it's not going to the right cells at all!! Any help?!?!

---------------------------------------------------------
Sub CheckCellFormat()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim C As Integer
Dim L As Integer
Dim Rng As Range
Dim DestinRng As Range
Dim D As Integer
Dim LineNo As Integer
Dim NewStartRng As Range


Set NewStartRng = Sheets("Note Formatter").Range("tabstart")
Set Rng = Sheets("Note Formatter").Range("tabstart")
Set DestinRng = Sheets("INPUT").Range("startlineinfo").Offset(0, 1)
LineNo = 1


L = Sheets("INPUT").Range("NoOfLines") + 2
C = Sheets("INPUT").Range("NoOfCols") + 1
D = -C

For x = 1 To L
For y = 1 To C
If Rng.Font.Bold = True Then
DestinRng.Value = "Bold" & Rng.Address
Else: DestinRng.Value = "Not Bold" & Rng.Address
End If
Set DestinRng = DestinRng.Offset(1, 0)


If Rng.Font.Italic = True Then
DestinRng.Value = "Italic" & Rng.Address
Else: DestinRng.Value = "Not Italic" & Rng.Address
End If

Set DestinRng = DestinRng.Offset(1, 0)

If Rng.HorizontalAlignment = xlLeft Then
DestinRng.Value = "Left" & Rng.Address
ElseIf Rng.HorizontalAlignment = xlRight Then
DestinRng.Value = "Right" & Rng.Address
ElseIf Rng.HorizontalAlignment = xlCenter Then
DestinRng.Value = "Cent" & Rng.Address
End If
Set DestinRng = DestinRng.Offset(1, 0)

Rng = Rng.Offset(0, 1)
Next y


Set NewStartRng = NewStartRng.Offset(1, 0)
Set Rng = NewStartRng
Next x


End Sub

 

[Cbrine]

I haven't reviewed your code entirely, but I think you are looking for a better way to loop through cells. The following code will cycle through each cell within a range of cells. Hope this helps.

Dim C as range

For each c in range("a1:D100")
If c.bold =True then
Your Code
End if

If c.italic = True then
Your Code
End if
Next

 

[bjwillingham]

cbrine has shown the best way to loop through each cell in a range. Though I don't know if you can control if it goes by row or column first. If you need to control the order you might try this:

'dont add 2 to L or 1 to C before hand.

Dest = 0

For x = 0 to L
For y = 0 to C


If Rng.offset(x,y).Font.Bold = True Then
V1 = "Bold" & Rng.offset(x,y).Address
Else: V1 = "Not Bold" & Rng.offset(x,y).Address
End If

If Rng.offset(x,y).Font.Italic = True Then
V2 = "Italic" & Rng.offset(x,y).Address
Else: V2 = "Not Italic" & Rng.offset(x,y).Address
End If

select case rng.offset(x,y).HorizontalAlignment
case xlLeft
V3 = "Left" & Rng.offset(x,y).Address
case xlRight
V3 = "Right" & Rng.offset(x,y).Address
case xlCenter
V3 = "Cent" & Rng.offset(x,y).Address
end select

DestinRng.Offest(Dest,0).value = V1
DestinRng.Offest(Dest +1 ,0).value = V2
DestinRng.Offest(Dest + 2,0).value = V3

Dest = Dest + 3

Next y
Next x

 

[cdhoughton]

Your code works fine for my problem, however I am curious as to why it works!!

My C and L values for columns and rows are variables depending on how many rows/columns a user enters. However, I have another two rows that are fixed (two header rows essentially) and another column (which the user enters data into). That is why I used c = cellref + 1 and L = cellref + 2.

I added +1 to the "L =" part of your code and that works fine, however could you point out exactly why, for my own benefit!!!