Alright what did I do wrong? Found this on the web, tried it and got a type mismatch. Just trying to make my code more concise by having the variables define themselves, for e.g. Y(10), Y(11), Y(12), Y(13), or Y10 ... Y13, so I'll have 4 variables at the end of the sub. Y10-13 are NOT the cell positions, but they are related. Look Below!
variable a refers to the actual row, so say Y10 = 400 is the only entry on row 5, row 7 has Y10 = 7 and Y12 = 3. I did not define this in the code because this section runs as function(a) and a is defined before the function itself such that the sub checks for a=1 to the last row.
Dim Y(10 To 13) As Integer 'these are just variables Dim a as long 'this is an arbitrary row number defined before the function For I = 10 to 13 'These are column numbers in the actual data range If .Worksheets("15SK").Cells(a, I) <> "" Then Y(I) = .Worksheets("15SK").Cells(a, I).value 'Y(I) basically takes the value of the cell (a,I) End If Next
I basically need a dynamic variable Y(I) or YI (in any form really) because later sections of my code calls for it. I'll share another section to state my point, but its difficult for me to translate what I'm doing in this next section:
For I = 10 To .Worksheets("15SK").Cells(2, Columns.Count).End(xlToLeft).Column 'All models If .Worksheets("15SK").Cells(a, I) <> "" Then 'If product model order > 0 'removed a section of code here j = j + 3 'step 3 If ItemType = "Adaptor" And Y > X1 And DUP <> 1 Then Y1 = Y1 + Y(I) If ItemType = "Adaptor" And Y1 > X1 Then 'adding another duplicate DOwb.Worksheets(1).Cells(j, 3) = .Worksheets("15SK").Cells(2, I) 'First DO product model row DOwb.Worksheets(1).Cells(j - 1, 1) = Y1 - X1 'Excess Qty to be charged DOwb.Worksheets(1).Cells(j - 3 - 1, 1) = Y(I) - (Y1 - X1) 'Making the previous row's Qty FOC by no. of CD - no. of previous DOwb.Worksheets(1).Cells(j - 1, 2) = "pcs" DOwb.Worksheets(1).Cells(j - 1, 3) = Application.WorksheetFunction.VLookup(DOwb.Worksheets(1).Cells(j, 3), .Worksheets("Catalogue").Range("catalogue"), 2, False) 'Model Description JFOC = j j = j + 3 'step 3 DUP = 1 'stops the duplication ElseIf Y1 = X1 Then JFOC = j End If Y1 = 0 End If
Sorry guys really new to VBA, or coding for the matter. Terrific help so far!