Retrieve cell value

[Martin]

The code below returns the position of a vertical page break. I would like
add some code that will return the value of cell i5, where "i" is the column
of each page. The print area is set to D7:CB21 and the cell values I need
are on D5:CB21. Can someone help me with this.

Thanks.

Sub TestPageBreaks()

Dim wksCurrent As Worksheet
Dim iVBreak As Integer
Dim strMessage As String

Set wksCurrent = ActiveSheet

For iVBreak = 1 To wksCurrent.VPageBreaks.Count
strMessage = "Address: " &
wksCurrent.VPageBreaks(iVBreak).Location.Address & _
vbCrLf & _
"Value: " & wksCurrent.VPageBreaks(iVBreak).Location.Cells(i,
5)
MsgBox strMessage, vbOKOnly, "Page Break Test"
Next iVBreak

Set wksCurrent = Nothing
End Sub

 

[Guest]

The value in any cell is:

Cells(i, j).Value

where j is the column number [1 - 256] and
where i is the row number [1 - 65536]

 

[Martin]

Gary thanks but how do I go about getting a cell value using VPageBreaks.

Thanks.

The value in any cell is:

Cells(i, j).Value

where j is the column number [1 - 256] and
where i is the row number [1 - 65536]

 

[Guest]

Well we already know that the row is row #5. Consider:


Sub gsnu()
Dim c As Long
Dim VertBreak As VPageBreak

For Each VertBreak In ActiveWindow.SelectedSheets.VPageBreaks
c = VertBreak.Location.Column
MsgBox (Cells(5, c).Value)
Next

End Sub

--
Gary''s Student

Gary thanks but how do I go about getting a cell value using VPageBreaks.

Thanks.

The value in any cell is:

Cells(i, j).Value

where j is the column number [1 - 256] and
where i is the row number [1 - 65536]
--
Gary's Student

The code below returns the position of a vertical page break. I would like
add some code that will return the value of cell i5, where "i" is the column
of each page. The print area is set to D7:CB21 and the cell values I need
are on D5:CB21. Can someone help me with this.

Thanks.

Sub TestPageBreaks()

Dim wksCurrent As Worksheet
Dim iVBreak As Integer
Dim strMessage As String

Set wksCurrent = ActiveSheet

For iVBreak = 1 To wksCurrent.VPageBreaks.Count
strMessage = "Address: " &
wksCurrent.VPageBreaks(iVBreak).Location.Address & _
vbCrLf & _
"Value: " & wksCurrent.VPageBreaks(iVBreak).Location.Cells(i,
5)
MsgBox strMessage, vbOKOnly, "Page Break Test"
Next iVBreak

Set wksCurrent = Nothing
End Sub

 

[Martin]

Gary,

Thanks it worked!

-M

Well we already know that the row is row #5. Consider:


Sub gsnu()
Dim c As Long
Dim VertBreak As VPageBreak

For Each VertBreak In ActiveWindow.SelectedSheets.VPageBreaks
c = VertBreak.Location.Column
MsgBox (Cells(5, c).Value)
Next

End Sub

--
Gary''s Student

Gary thanks but how do I go about getting a cell value using VPageBreaks.

Thanks.

The value in any cell is:

Cells(i, j).Value

where j is the column number [1 - 256] and
where i is the row number [1 - 65536]
--
Gary's Student

The code below returns the position of a vertical page break. I would like
add some code that will return the value of cell i5, where "i" is the column
of each page. The print area is set to D7:CB21 and the cell values I need
are on D5:CB21. Can someone help me with this.

Thanks.

Sub TestPageBreaks()

Dim wksCurrent As Worksheet
Dim iVBreak As Integer
Dim strMessage As String

Set wksCurrent = ActiveSheet

For iVBreak = 1 To wksCurrent.VPageBreaks.Count
strMessage = "Address: " &
wksCurrent.VPageBreaks(iVBreak).Location.Address & _
vbCrLf & _
"Value: " & wksCurrent.VPageBreaks(iVBreak).Location.Cells(i,
5)
MsgBox strMessage, vbOKOnly, "Page Break Test"
Next iVBreak

Set wksCurrent = Nothing
End Sub