MAcro To split a number dividing by 40

Hi All,

I attached a sheet for your reference. Please check.

I am trying to write a macro which will help to divide a whole number by 40 and than place the reminder in next rows kindly help for the same.

Thanks,
Vignesh kumar.S

Hi, vigneshkumar,

give this code a try:

Sub 911415()
Dim lngCounter As Long
Dim lngRow As Long
Dim lngLastCol As Long
Dim lngMod As Long
Dim blnWrite As Boolean

Const clngDATA As Long = 4
Const clngDIV As Long = 40
lngRow = clngDATA

For lngCounter = 4 To Cells(clngDATA, Columns.Count).End(xlToLeft).Column
  lngMod = Int(Cells(clngDATA, lngCounter).Value / clngDIV)
  If lngMod > 0 Then
    lngRow = lngRow + 1
    With Cells(lngRow, lngCounter)
      .Value = Cells(clngDATA, lngCounter).Value Mod clngDIV
      .Offset(0, -lngMod).Resize(1, lngMod).Value = clngDIV
    End With
  End If
Next lngCounter
End Sub

Ciao,
Holger

Hi, Vignesh ,

just copy sheet1, clear the rows under Row 4 and then run the macro.

i will rephrase my question i am trying to split any number in the form of 40

What does the macro do? Personally I wonīt let data start in the middle of nowhere but as this is done in the attachment the code takes care of the situation in Sheet1 and hopefully doubles up what I thought was the desired outcome.

Ciao,
Holger

Hi, Vignesh,

if i am confusing

Whatīs the good of posting a workbook which should show the situation as is and as desired and then come back because you want something else than what is shown in the workbook? Even with what you tell right now itīs unclear to me what you want: all data in one row or all data cascading down under the original? Only show numbers if they are greater than 40 or show all rests?

Regarding your attached workbook and the sample given there:

Sub EF911415_2a()
Dim lngCounter As Long
Dim lngRow As Long
Dim lngLastCol As Long
Dim lngMod As Long
Dim blnWrite As Boolean

Const clngDATA As Long = 4
Const clngDIV As Long = 40
lngRow = clngDATA

For lngCounter = 4 To Cells(clngDATA, Columns.Count).End(xlToLeft).Column
  lngMod = Int(Cells(clngDATA, lngCounter).Value / clngDIV)
  If lngMod > 0 Then
    lngRow = lngRow + 1
    Cells(lngRow, lngCounter).Value = Cells(clngDATA, lngCounter).Value Mod clngDIV
  End If
Next lngCounter
End Sub

Sub EF911415_2b()
Dim lngCounter As Long
Dim lngRow As Long
Dim lngLastCol As Long
Dim lngMod As Long
Dim blnWrite As Boolean

Const clngDATA As Long = 4
Const clngDIV As Long = 40
lngRow = clngDATA + 1

For lngCounter = 4 To Cells(clngDATA, Columns.Count).End(xlToLeft).Column
  lngMod = Int(Cells(clngDATA, lngCounter).Value / clngDIV)
  If lngMod > 0 Then
    Cells(lngRow, lngCounter).Value = Cells(clngDATA, lngCounter).Value Mod clngDIV
  End If
Next lngCounter
End Sub

Sub EF911415_2c()
Dim lngCounter As Long
Dim lngRow As Long
Dim lngLastCol As Long
Dim lngMod As Long
Dim blnWrite As Boolean

Const clngDATA As Long = 4
Const clngDIV As Long = 40
lngRow = clngDATA + 1

For lngCounter = 4 To Cells(clngDATA, Columns.Count).End(xlToLeft).Column
  If Cells(clngDATA, lngCounter).Value > 0 Then
    lngMod = Int(Cells(clngDATA, lngCounter).Value / clngDIV)
    Cells(lngRow, lngCounter).Value = Cells(clngDATA, lngCounter).Value Mod clngDIV
  End If
Next lngCounter
End Sub

as I mentioned before keep a number 90 in one cell b6 then if i run the macro than it should automatically place in cell c4 40 in cell c5 40 and in cell c6 10

No, as the attached workbook showed numbers in one row and the results underneath.

Holger

Hi, Vignesh,

the first macro code showed a way using Offset and Resize (although result was in a row, not a column).

Ciao,
Holger