Working with Array

blelli

Board Regular
Joined
Jul 21, 2013
Messages
73
Dears,

I've a very important question now... :)

I have a list of "n" items (lets imagine that I have 45 items on this list)
The "screen" of my VBA project has only 5 lines, divided into 2 columns. So, I can show only 10 items of my list per page.

How can I do to show the first 10 values at the first page, then if the user click on the NEXT PAGE BTN, the system will show him the items from 11 to 21... if he clicks on the PREV PAGE BTN the system will show him the entries from 1 to 10... and so on...?



Thank you so much
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I have a list of "n" items (lets imagine that I have 45 items on this list)
The "screen" of my VBA project has only 5 lines, divided into 2 columns. So, I can show only 10 items of my list per page.
Where is this list... in some cells on a worksheet (if so, which worksheet and what range), in a VB array (if so, what is the name of the array), somewhere else (if so, where)?

What is the range address for the 5 rows by 2 columns output table?
 
Upvote 0
Where is this list... in some cells on a worksheet (if so, which worksheet and what range), in a VB array (if so, what is the name of the array), somewhere else (if so, where)?

What is the range address for the 5 rows by 2 columns output table?


The list is in a VB Array, named: DataLinkList
The range address is from C1 to D5
 
Upvote 0
The list is in a VB Array, named: DataLinkList
Can you show us your code as it would be much easier to mesh new code into what you already have than to make it up and have you figure out how to integrate it into your code on your own.
 
Upvote 0
Can you show us your code as it would be much easier to mesh new code into what you already have than to make it up and have you figure out how to integrate it into your code on your own.

The fact is that I don't even know how to start... I don't have anything done yet.

Thank you
 
Upvote 0
The fact is that I don't even know how to start... I don't have anything done yet.
Okay, how do you plan to populate your DataLinkList array then? Where are the values that will go into the array located at?

Just so you know, it is difficult to write code if you do not know the layout of the data, how it is to be used or what is planned for it... keep in mind that we know none of these things unless you tells us.
 
Last edited:
Upvote 0
Okay, how do you plan to populate your DataLinkList array then? Where are the values that will go into the array located at?

Just so you know, it is difficult to write code if you do not know the layout of the data, how it is to be used or what is planned for it... keep in mind that we know none of these things unless you tells us.

Please, check the DataLInkList array below
DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL").

I would like to write these texts in a range from C1:D5, so I've only 10 cells to place my values...
I've 2 buttons, one called NEXT and the other called PREVIOUS... so clicking on NEXT will show the values from 11 to 21, next again will show the values from 31 to 42
 
Upvote 0
Please, check the DataLInkList array below
DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL").

I would like to write these texts in a range from C1:D5, so I've only 10 cells to place my values...
I've 2 buttons, one called NEXT and the other called PREVIOUS... so clicking on NEXT will show the values from 11 to 21, next again will show the values from 31 to 42
Okay, copy/paste the following code (exactly as I have it) into a separate module and set your buttons to use them (that names of the macros will tell you what the code does)...
Code:
[table="width: 500"]
[tr]
	[td]Global DataLinkListIndex As Long

Sub NextPageButton_Click()
  Dim R As Long, DataLinkList As Variant, Col1 As Variant, Col2 As Variant
  Static DataLinkListIndex As Long
  DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL")
  For R = 1 To 5
    If DataLinkListIndex + R - 1 <= UBound(DataLinkList) Then
      Cells(R, "C").Value = DataLinkList(DataLinkListIndex - 1 + R)
    Else
      Cells(R, "C").Value = ""
    End If
    If DataLinkListIndex + R + 4 <= UBound(DataLinkList) Then
      Cells(R, "D").Value = DataLinkList(DataLinkListIndex + 4 + R)
    Else
      Cells(R, "D").Value = ""
    End If
  Next
  DataLinkListIndex = DataLinkListIndex + 10
  If DataLinkListIndex > UBound(DataLinkList) Then DataLinkListIndex = 0
End Sub

Sub PreviousPageButton_Click()
  Dim R As Long, DataLinkList As Variant, Col1 As Variant, Col2 As Variant
  Static DataLinkListIndex As Long
  DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL")
  For R = 1 To 5
    If DataLinkListIndex + R - 1 <= UBound(DataLinkList) Then
      Cells(R, "C").Value = DataLinkList(DataLinkListIndex - 1 + R)
    Else
      Cells(R, "C").Value = ""
    End If
    If DataLinkListIndex + R + 4 <= UBound(DataLinkList) Then
      Cells(R, "D").Value = DataLinkList(DataLinkListIndex + 4 + R)
    Else
      Cells(R, "D").Value = ""
    End If
  Next
  DataLinkListIndex = DataLinkListIndex - 10
  If DataLinkListIndex < 0 Then DataLinkListIndex = Application.MRound(UBound(DataLinkList), 10)
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Okay, copy/paste the following code (exactly as I have it) into a separate module and set your buttons to use them (that names of the macros will tell you what the code does)...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Global DataLinkListIndex As Long

Sub NextPageButton_Click()
  Dim R As Long, DataLinkList As Variant, Col1 As Variant, Col2 As Variant
  Static DataLinkListIndex As Long
  DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL")
  For R = 1 To 5
    If DataLinkListIndex + R - 1 <= UBound(DataLinkList) Then
      Cells(R, "C").Value = DataLinkList(DataLinkListIndex - 1 + R)
    Else
      Cells(R, "C").Value = ""
    End If
    If DataLinkListIndex + R + 4 <= UBound(DataLinkList) Then
      Cells(R, "D").Value = DataLinkList(DataLinkListIndex + 4 + R)
    Else
      Cells(R, "D").Value = ""
    End If
  Next
  DataLinkListIndex = DataLinkListIndex + 10
  If DataLinkListIndex > UBound(DataLinkList) Then DataLinkListIndex = 0
End Sub

Sub PreviousPageButton_Click()
  Dim R As Long, DataLinkList As Variant, Col1 As Variant, Col2 As Variant
  Static DataLinkListIndex As Long
  DataLinkList = Array("RCWVWD MSGS", "SEDSDND MSG", "WEASDTHESR", "TSWIP", "ATASDIS", "RETASDURN", "ASSDTS LOG", "DEPARTURE CLX", "OCEANICOL CLX", "AIRPORTS", "AIRWAYS", "SERIAL NUMBER", "PRODUCT ID", "PILOT", "DEPARTURE", "ARRIVAL", "SECTOR", "WAYPOINT", "DATASET", "WOOD", "WING", "DOOR", "LANDING GEAR", "ABEAN", "RIBS", "COWN", "FLAPS", "LEDGE", "CHOPER", "TERMINAL", "IATA", "ICAO", "ANE", "JOHN", "MICHAEL", "DASD", "EAT", "ESTERN", "CRIS", "TO", "FROM", "NEAR", "CLOSE", "KABAL")
  For R = 1 To 5
    If DataLinkListIndex + R - 1 <= UBound(DataLinkList) Then
      Cells(R, "C").Value = DataLinkList(DataLinkListIndex - 1 + R)
    Else
      Cells(R, "C").Value = ""
    End If
    If DataLinkListIndex + R + 4 <= UBound(DataLinkList) Then
      Cells(R, "D").Value = DataLinkList(DataLinkListIndex + 4 + R)
    Else
      Cells(R, "D").Value = ""
    End If
  Next
  DataLinkListIndex = DataLinkListIndex - 10
  If DataLinkListIndex < 0 Then DataLinkListIndex = Application.MRound(UBound(DataLinkList), 10)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top