Click here to Skip to main content
15,867,330 members

Comments by Mat 257 (Top 7 by date)

Mat 257 8-Nov-23 1:51am View    
thanks.
therefore the function return null and i have to re set as 'void' function right? (using a c++ definitions)
it just perform copy and paste task. correct?
Mat 257 13-May-22 13:42pm View    
very thank you for this detailed help. just let me try to explain the code meaning.

i hope to clarify:
the main function of this code is to
1) purpose: determine a check ranges of data between row 1 and row 2.
these are two row numbers.
the row numbers are determined in column (column n. 5) by 'cosacerco' look up (.find).
cosacerco it is a string data.
this new range is a fixed sub-range in column 5 between newriga and addressdatafin rows.
therefore the new range to be analyzed is inner 'searchrange' and is restricted to row1 and row2

it starts reading data by selecting cells(newriga,5) .

this cell value is look up along the entire column (5);
row1 find the first occurance in top down direction
while row2 looks the same value but bottom up direction.

2) purpose: to scroll down each cell in new range between row1 and 2 and populate 2 arrays upon conditions met.
MatrNumeri() hold all values that may be found during scroll.

each cells value may have fisrt part with 1/2 numeric value digit separated by the rest of string content with "_" delimiter (something like "12_aabbcccdd4g&&$$....")

of course not all cell have such character ("abbgg12///"), and when instr function return nothing, the error occur.

second array MatrSomme() it's an array beside MatrNumeri(); it has same size.

so if one element of MatrNumeri(i) is found in range string value , the same position of MatrSomme( ) is populated by a progressive sum integer.

MatrNumeri hold a series of numeric values that match first numeric part of range value;
eg. MatrNumeri(2) where MatrNumeri(0)="5", MatrNumeri(1)="16", MatrNumeri(2)="8"

loop (i) srcoll over all ranges between row1 and row2,
loop (z) scroll over all MatrNumeri elements, i.e. fro 0 to 2

let's say range value = "5_aabbcccdd4g&&$$...."

the numeric string compared with MatrNumeri(0) element is variable 'Temporary '; in this case it is = "5", first numeric value of range content.

since MatrNumeri(0) is = 'Temporary'= "5" then MatrSomme(0) is populated by an integer.
for my question, it doesnt matter where it comes from.

the final result consists , in this case, of two arrays;
MatrNumeri(0)="5", MatrNumeri(1)="16", MatrNumeri(2)="8"
MatrSomme(0)=5000, MatrSomme(1)=300, MatrSomme(2)=26657.

as i told, if "_" is missing, 'Temporary' is nothing and the error pops out.

so i simply would step over next loop if this mismatching occur.
thats why i thought to use on error goto ... next loop

precisely , error handling work fine at first error occurance but in second , it stucks
Mat 257 13-May-22 8:03am View    
hi, and thanks for yuor reply, please find below full subroutine code:


Sub TrasfDaTI()
Dim FindRange As Range, AddressDataFin As Integer, riga As Integer
Set FindRange = ActiveSheet.Range("E2:O445")
AddressDataFin = 445
riga = 3

Dim MatrNumeri() As Variant, Somma As Integer, SearchRange As Range, NewRiga As Integer
Dim z As Integer, i As Integer
Dim Row1 As Integer, Row2 As Integer, Trovato As Range, CosaCerco As String

'ReDim MatrNumeri(2)
'MatrNumeri(0) = "4"
'MatrNumeri(1) = "16"
'MatrNumeri(2) = "11"

NewRiga = riga
'REIMPOSTO IL RANGE DI DATI UNA CELLA PIù SOPRA
Set SearchRange = ActiveSheet.Range(Cells(NewRiga - 1, 5), Cells(AddressDataFin, 5))


Cells(NewRiga, 5).Select
Dim MatrSomme() As Variant, Temporary As String

Do Until ActiveCell.Value = ""

CosaCerco = ArrangeStringDate(ActiveCell.Value)
Row1 = SearchRange.Find(what:=CosaCerco, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
Row2 = SearchRange.Find(what:=CosaCerco, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

MatrNumeri() = MatrixNumeriProdot(FindRange, Row2, Row1) '<----
ReDim MatrSomme(UBound(MatrNumeri))
For i = Row1 To Row2
On Error GoTo ErrorHandler
Temporary = Left(Range("N" & i).Value, (InStr(1, Range("N" & i).Value, "_", vbTextCompare) - 1))
On Error GoTo -1
For z = LBound(MatrNumeri) To UBound(MatrNumeri)
If Temporary = MatrNumeri(z) Then
Dim ImportoSomma As Integer
ImportoSomma = CInt(Cells(i, 15).Value)
MatrSomme(z) = MatrSomme(z) + ImportoSomma
End If
Next z
qui:
Next i
Stop
Erase MatrNumeri()
Erase MatrSomme()
Cells(Row2, 5).Offset(1, 0).Select

Loop
'*********************
ErrorHandler:
GoTo qui
'*********************

End Sub
Mat 257 21-Feb-22 11:01am View    
thanks for reply, actually is what function meant to do. after collected data upon certain condition happening, it must append the data in a new row but columns must be the same, always 3 columns (0 to 2)
Mat 257 21-Feb-22 9:06am View    
thanks