Working with Array

B

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
blelli said:
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.
Click to expand...
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
Rick Rothstein said:
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?
Click to expand...


The list is in a VB Array, named: DataLinkList
The range address is from C1 to D5
 
Upvote 0
blelli said:
The list is in a VB Array, named: DataLinkList
Click to expand...
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
Rick Rothstein said:
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.
Click to expand...

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

Thank you
 
Upvote 0
blelli said:
The fact is that I don't even know how to start... I don't have anything done yet.
Click to expand...
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
Rick Rothstein said:
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.
Click to expand...

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
blelli said:
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
Click to expand...
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
Rick Rothstein said:
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]
Click to expand...


Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create Formula to randomly Sequence/Prioritize a list of items
Replies
3
Views
184
dreid1011
dreid1011
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
446
Akuini
Akuini
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
FrumpyJones
  • Question Question
Is there a way to stop COPY/PASTE but still allow manual entries and use of drop down lists.
Replies
5
Views
255
FrumpyJones
FrumpyJones
C
  • Solved
Drop-down menus in tables and removing used items from that menu
2
Replies
11
Views
234
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

Share this page

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