I'm new to vba and I'm having trouble writing in my code that each loop, should use as input the value of the previous loop.
Specifically, in the table below , what I want to achieve is get the result of X = E5 - F5, then do X - G5 and so on until column W.
Below my code as well
I'm sure the answer is quite simple but I cannot wrap my head around it. Thank you in advance!
r = 5
c = 6
Dim UseV As Long
Dim StartV As Long
Dim EndV2 As Long
Dim EndV As Long
Do While r <= LastFCrow2 'loop through all rows
If FCws.Cells(r, 2).Value <> PrevYM And FCws.Cells(r, 4).Value <> 0 And FCws.Cells(r, 3).Value = "PldOrd" Then 'only test rows of the new dataset, where contract exists, only for forecasted qty
StartV = FCws.Cells(r, 5).Value
Do While c <= LastFCcol 'loop through all columns: all values in one row
UseV = FCws.Cells(r, c).Value
EndV = StartV - UseV 'can't store somewhere the EndV so that it will be used as the startV in the next iteration
If EndV > 0 Then 'go to next month,format top and bottom line
With FCws.Cells(r, c) _
.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End If
If EndV < 0 Then ' run out during month, format diagonal line
With FCws.Cells(r, c) _
.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Exit Do 'loop will exit to next r aftet the current r,c combination that first satisfies the EndV<0 outcome
End If
If EndV = 0 Then ' run out after month, format top and right side border
With FCws.Cells(r, c) _
.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With FCws.Cells(r, c) _
.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Exit Do 'loop will exit to next r aftet the current r,c combination that first satisfies the EndV=0 outcome
End If
c = c 1
Loop
c = 6
End If
r = r 4
Loop
CodePudding user response：
Something like this maybe?
c = 4
X = Cells(5, c).Value - Cells(5, c 1).Value
LastFCcol = 10
Do While c <= LastFCcol
'Do something with X here
X = X - Cells(5, c 1).Value
c = c 1
Loop
CodePudding user response：
Here is a longer answer, with some guidance on how to clearly use the code itself to describe what you're doing.
- Use constants for "magic numbers", such as columns for specific data. Someone looking at your code will see a "2" and won't automatically relate that to the dataset value.
- Declare ALL your variables and use
Option Explicit - Find any common code - in your case, it's setting the cell borders - and use it in a common function or sub.
One thing I believe may be an issue is storing the lastQty value because it should be associated with a specific dataset. Your data may be well-ordered and will ALWAYS have dataset lines right next to each other. Using the Collection may be overkill in this case, but I'm hoping it shows another way to access the previous quantity.
Option Explicit
Sub FormatQuantities()
Dim fcWS As Worksheet
Set fcWS = ThisWorkbook.Sheets("FC")
With fcWS
Dim lastRow As Long
Dim lastCol As Long
lastRow = .Cells(.Rows.Count, 1).End(xlUp).row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Const FIRST_ROW As Long = 5 'why is this 5 and not 2 ?
Const FIRST_COL As Long = 6
Const DATASET_COL As Long = 2
Const MRP_ELEM_COL As Long = 3
Const CONTRACTS_COL As Long = 4
Const OPEN_QTY_COL As Long = 5
Dim lastQty As Collection
Set lastQty = New Collection
Dim prevDataset As String
Dim r As Long
Dim c As Long
For r = FIRST_ROW To lastRow
'--- only test rows of the new dataset, where contract exists,
' only for forecasted qty
If .Cells(r, MRP_ELEM_COL).Value = "PldOrd" Then
If (.Cells(r, CONTRACTS_COL).Value > 0) And _
(.Cells(r, DATASET_COL).Value <> prevDataset) Then
'--- set the starting quantity to either zero or the
' previously calculated endQty
Dim startQty As Long
If CollectionContains(lastQty, .Cells(r, DATASET_COL).Value) Then
startQty = lastQty(.Cells(r, DATASET_COL).Value)
Else
startQty = .Cells(r, OPEN_QTY_COL).Value
End If
'--- format the cell based on the relative quantity
For c = FIRST_COL To lastCol
Dim usedQty As Long
Dim endQty As Long
endQty = startQty - .Cells(r, c).Value
Select Case endQty
Case Is < 0
FormatCellBorder .Cells(r, c), xlDiagonalDown
Case Is = 0
FormatCellBorder .Cells(r, c), xlEdgeRight
FormatCellBorder .Cells(r, c), xlEdgeTop
Case Is > 0
FormatCellBorder .Cells(r, c), xlEdgeTop
End Select
Next c
'--- store the ending quantity for this dataset to be
' used the next time we find it
lastQty.Add endQty, .Cells(r, DATASET_COL).Value
End If
End If
Next r
End With
End Sub
Sub FormatCellBorder(ByRef theCell As Range, ByVal index As XlBordersIndex)
With theCell.Borders(index)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
Function CollectionContains(myCol As Collection, checkVal As Variant) As Boolean
'--- from: https://analystcave.com/vba-collection/
On Error Resume Next
CollectionContains = False
Dim it As Variant
For Each it In myCol
If it = checkVal Then
CollectionContains = True
Exit Function
End If
Next
End Function