current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Sorry for not been enough clear in my previous message!

Here is the situation A user have the possibility to add new row in an Excel Array. I would like then to stock the new parameter in the last line of a dynamic array on macro in order to make other calculations.

For example: I have an Array of 2 columns: Parameter and Value Parameter <-- B1 column Param1 Param2 Param3

Value <-- C1 column Val1 Val2 Val3

Hereafter what I have done but it doesn't work!

Dim RowCount As Integer
RowNumber = Sheets("Feuil1").Range("C1").End(xlDown).row
'MsgBox "Rows:" & RowNumber-1

Dim tb_val() As Integer
ReDim tb_val(RowNumber - 1)
Dim lc() As Integer

For i = 1 To RowNumber
    lc = PathFinder("Feuil1", Cells(i, 2).Value)
    tb_val(i - 1) = Sheets("Feuil1").Cells(lc(1), lc(2) + 1).Value 
Next i

P.S: PathFinder("worksheet1", "word1") send arr(2) with cells details -column & row- of "word1" found in "worksheet1" 

Function PathFinder(sheet1 As String, word1 As String) As Integer()
    Dim rng As Range
    Dim rngFound As Range
    Dim temp(2) As Integer

    Set rng = Sheets(sheet1).Range("A:B")
    Set rngFound = rng.Find(word1, LookAt:=xlWhole, SearchOrder:=xlByRows)

    If rngFound Is Nothing Then
        MsgBox "not found"
    Else:
        temp(1) = rngFound.row
        temp(2) = rngFound.column
    End If
    PathFinder = temp
End Function

Thanks

share|improve this question
1  
I truly can't say that I understand your question completely, but it seems like you might be able to use Dynamic ranges (support.microsoft.com/kb/830287) and the worksheet_change event to accomplish what it is you're looking to do... Otherwise, please update your question and explain your situation better... –  John Bustos Apr 1 at 15:22

2 Answers 2

up vote 0 down vote

Well, if I understand correctly, it sounds like you want to do this:

ReDim Preserve arr(2)
arr(2) = val2

The ReDim will resize the array. The Preserve keeps the values that are already in the array (otherwise they will be re-initialized.

share|improve this answer
up vote 0 down vote

Not sure i fully understand, but here's my opinion : RowNumber is (i think) wrong ; use 'With' ; the second example shows how to make a very fast array.

Dim RowCount As Long 'Long is faster , and you never know how many lines you need
Dim Sh as worksheet
set Sh = thisworkbook.Sheets("Feuil1")

with Sh
    RowNumber = .Range(.rows.count,"C").End(xlUp).Row  ' "C" can be replaced by 3, same effect.
    'MsgBox "Rows:" & RowNumber
end with

Dim tb_val() As Long
ReDim tb_val( 1 to RowNumber)
Dim lc() As Long

For i = 1 To RowNumber
    lc(i) = PathFinder("Feuil1", Cells(i, 2).Value) 'no idea what this does ???
    tb_val(i) = Sh.Cells(lc(1), lc(2) + 1).Value 'must be mistacke, no (i) in lc() ??
Next i

Ok, i don't understand all your code, still consider this:

Dim MyArray() as Variant 'must be variant for this effect
Redim MyArray (1 to RowNumber , 1 to 3) 'if only 3 columns, Rownumber is the same as above.
with sh 'same Sh as in example above
    MyArray= .Range ( .cells( 1,1 ) , .cells ( rownumber,3 ) ).value 'stocks all the cells (in 3 columns in example in a VBA Array for faster calculs later
    'this way any loop or calculation is way faster, using Myarray(y,x) instead of Sh.cells(y,x).value
end with

'Long Loop + calculs... for i=1 to Rownumber ....

Sh.range( "A1:C" & RowNumber).Value = MyArray 'Writes Back the VBA Array to the Worksheet (only needed if changes made inside the values of the array)

'range ("A1:C" & RowNumber)  has the same effect as Range(cells(1,1) , cells(rownumber,3))    from above , just wanted to say
share|improve this answer
    
I've just added details on PathFinder function... –  ThinkMAL Apr 2 at 11:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.