Explanation of Each line of code to figure out column name (like A,B,C,etc)

[paradise]

Hi,
I want to do little modification in below code for which I would like to figure out column name.Secondly,I want the modification regarding numbers which is not taking here when the numbers are in decimal like 250.45 i.e it is not subtracting the number after decimal.Hence,taking whole number only for subtraction.

I hope some vba experts will help in this regard.

Sub test()
Dim sh As Worksheet, rg As Range, cell As Range, qty As Range
Dim rgP As Range, rgQ As Range
Dim qty1 As Long, qty2 As Long

Set sh = Sheets("Temp")

With sh
Set rg = .Range("W3:" & Cells(3, Columns.Count).End(xlToLeft).Address)
End With

For Each cell In rg.SpecialCells(xlConstants)

    With sh.Columns(1)
        .Replace cell.Value, True, xlWhole, , False, , False, False
        Set rgP = .SpecialCells(xlConstants, xlLogical)
        .Replace True, cell.Value, xlWhole, , False, , False, False
    End With
    
    With rgP.Offset(0, 1)
        If cell.Offset(1, 0).Value <> "" Then
        .Replace cell.Offset(1, 0).Value, True, xlWhole, , False, , False, False
        Set rgQ = .SpecialCells(xlConstants, xlLogical).Offset(0, 2)
        .Replace True, cell.Offset(1, 0).Value, xlWhole, , False, , False, False
        Else
        Set rgQ = rgP.Offset(0, 3)
        End If
    End With

    qty1 = cell.Offset(2, 0).Value
    qty2 = 0
    Set qty = rgQ(rgQ.Rows.Count, 1)
        Do
        If qty.Row <> rgP(1, 1).Row Then
            If qty2 <= qty1 And qty1 > qty2 + qty.Value Then
                qty2 = qty2 + qty.Value
                sh.Cells(qty.Row, cell.Column).Value = qty.Value
            Else
                sh.Cells(qty.Row, cell.Column).Value = qty1 - qty2
                Exit Do
            End If
        Else
            If qty.Value - (qty1 - qty2) < 0 Then
                sh.Cells(qty.Row, cell.Column).Value = qty.Value - (qty1 - qty2)
            Else
                sh.Cells(qty.Row, cell.Column).Value = qty1 - qty2
            End If
            Exit Do
        End If
        Set qty = qty.Offset(-1, 0)
        Loop
        
Next

End Sub

 

[Marc L]

Hi, according to your guessing challenge - you should better follow forum rules ! - just check the variable used​

if at least obviously is a decimal data type …​

​

 

[paradise]

Shall I post the file.

 

[Marc L]

Yes as it's better easier for any helper to give it a try, with your best explanation …​

​

 

[paradise]

Sorry for the delay.

Here,I have attached the file with macro.When u click the breakup button it gives the result X9 below and across.The sum.of total must match with X8 and across with that of below of each column from X9 .The decimal part is not done.Another tab for clearing the data from X9 down and across.

You can freely modify the code that makes much easier to understand.

With due respect,I hope your kind help in this regard.

 

[Marc L]

(Removed 'cause of wild cross posting again !)​

​

Already solved in another Excel forum : this thread is closed …​

​