PDA

View Full Version : [SOLVED] Array



LordRaper
12-07-2014, 02:34 AM
i have code like this


Option Base 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, u As Long
t = Array("John", "Mark", "Darko","Frank","Georg","Petar","Bob","Natali","Tina")
For u = LBound(t) To UBound(t)
Range("A:A").Replace u, t(u)

Next
End Sub

code like this work perfect but
if i add more name in array i get wrong resualt...:banghead:
i need 15 name
any help is good
txn

SamT
12-07-2014, 02:34 PM
What wrong result do you get?

Are you trying to replace numbers (1 thru 15) with Names?

snb
12-07-2014, 02:47 PM
Of course 11 wil be replaced by "JohnJohn"

But you also should avoid doing this in the eventprocedure 'SelectionChange'.

Blade Hunter
12-07-2014, 05:23 PM
You could do ubound to lbound to counter the "johnjohn" problem but I would probably avoid replace in this instance and test the cell value, too much potential for error.

Do you have a few rows of sample data?

LordRaper
12-08-2014, 07:29 AM
i fix this problem like this

Option Base 1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, u As Long
t = Array("John", "Mark", "Darko","Frank","Georg","Petar","Bob","Natali","Tina")
For u = LBound(t) To UBound(t)
Range("A:A").Replace u, t(u),xlwhole

Next End Sub

snb
12-08-2014, 07:36 AM
I'd use


Sub M_snb()
sn=Array("","John", "Mark", "Darko","Frank","Georg","Petar","Bob","Natali","Tina")
For j=1 to ubound(sn)
columns(1).Replace j, sn(j), 1
Next
End Sub

SamT
12-08-2014, 07:58 AM
Just a guess, but I'd say that JohnJohn was the problem.