Hi Guys,
I am new to VBA and trying to get below done through vba code.
Column "A" has 1 to 100 numbers and in column "B", i want result if the number is divisor of 3 or if the number is divisor of 5 or both.
I have done through formula as below
=IF(AND(MOD(A2,3)=0,MOD(A2,5)=0),"3-5",IF(MOD(A2,3)=0,"3",IF(MOD(A2,5)=0,"5","")))
Thanks
Re: VBA Code for Getting Divisior
see this
Sub findmod()
Set myrange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each cell In myrange
If cell.Value Mod 3 = 0 Then cell.Offset(0, 1) = 3
Next cell
End Subdevelop similar code for the other validations and then post back ur code for the benefit of everybody
Re: VBA Code for Getting Divisior
Or maybe
Sub test()
Dim lRow As Long
With Sheet1
lRow = .Cells(1).CurrentRegion.Columns(1).Rows.Count
With .Range("b2:b" & lRow).Cells
.Formula = "=IF(AND(MOD(A2,3)=0,MOD(A2,5)=0),""3_5"",IF(MOD(A2,3)=0,""3"",IF(MOD(A2,5)=0,""5"","""")))"
.Value = .Value
.HorizontalAlignment = xlCenter
End With
End With
End SubRe: VBA Code for Getting Divisior
Thanks pangolin.. exactly what I was looking for...
Thanks KjBox.. I did not intend to put the formula to calculate.
My Final Code.
Sub XLMod()
Set myrange = Sheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each cell In myrange
If cell.Value Mod 3 = 0 And cell.Value Mod 5 = 0 Then
cell.Offset(0, 1) = "3-5"
ElseIf cell.Value Mod 3 = 0 Then
cell.Offset(0, 1) = "3"
ElseIf cell.Value Mod 5 = 0 Then
cell.Offset(0, 1) = "5"
End If
Next cell
End SubRe: VBA Code for Getting Divisior
Did you try my code? It does put the formula there but then immediately removes the formula and replaces it with the result of the formula as a value.
Don’t have an account yet? Register yourself now and be a part of our community!