Need solution regarding Mod combined with Lcm in macro

[johnny5837]

Hello guys,


I’m new in VBA and can’t figure out how should I move forward. I’m using the below mentioned macro to calculate different requirements. It works fine if the frequency values in column B are divisible with lowest frequency, for example 100, 200, 400 & 500. However if there is an non-common number in my range, the result is incorrect.

 Sub MaxFreq()
'Speed up
Application.ScreenUpdating = False


'Clear column D & E
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With
    
Range("D2:F" & LastRow).ClearContents


'Numbers needed as Integer
Dim i, x, NumRows, MyRows, LowestFreq, HighestFreq As Integer


'Find the highest frequency
HighestFreq = Application.WorksheetFunction.max(Range("B:B"))


'Find lowest frequency
LowestFreq = Application.WorksheetFunction.min(Range("B:B"))


'Use frequencies as string
Dim myResult As String


'Find the number of rows to use it in loop in column B
NumRows = Range("B1", Range("B1").End(xlDown)).Rows.Count


'Use LCM function
MyRows = WorksheetFunction.Lcm(Range("B2:B" & NumRows)) / LowestFreq


'Loop both x & i to get the results
For x = 2 To NumRows


    For i = 1 To MyRows
    myResult = Range("B" & x)
    
    If LowestFreq * i = LowestFreq Then
    Range("E" & i + 1) = LowestFreq
    End If


    If LowestFreq * i Mod myResult = 0 Then
        If Range("E" & i + 1) = "" Then
        Range("E" & i + 1) = Range("B" & x)
        Range("F" & i + 1) = LowestFreq
        Else
        Range("E" & i + 1) = Range("E" & i + 1) & ", " & Range("B" & x)
        Dim text_string As String
        text_string = Range("E" & i + 1)
        Range("F" & i + 1) = WorksheetFunction.Lcm(Split(text_string, ", "), LowestFreq)
        End If
    End If
    Range("D" & i + 1) = "Service # " & i


    Next i
Next x


'Show results
Application.ScreenUpdating = True


End Sub

My sheet has the followings with correct result:

Excel 2010
	B	C	D	E	F
1	Freq		Number of Services	Type of Services	Job plan
2	100		Service # 1	100	100
3	200		Service # 2	100, 200	200
4	400		Service # 3	100	100
5	500		Service # 4	100, 200, 400	400
6			Service # 5	100, 500	500
7			Service # 6	100, 200	200
8			Service # 7	100	100
9			Service # 8	100, 200, 400	400
10			Service # 9	100	100
11			Service # 10	100, 200, 500	1,000
12			Service # 11	100	100
13			Service # 12	100, 200, 400	400
14			Service # 13	100	100
15			Service # 14	100, 200	200
16			Service # 15	100, 500	500
17			Service # 16	100, 200, 400	400
18			Service # 17	100	100
19			Service # 18	100, 200	200
20			Service # 19	100	100
21			Service # 20	100, 200, 400, 500	2,000
CALCULATION

And here is the incorrect result with other frequencies:

Excel 2010
	B	C	D	E	F
1	Freq		Number of Services	Type of Services	Job plan
2	300		Service # 1	300	300
3	750		Service # 2	300	300
4	1,500		Service # 3	300	300
5			Service # 4	300	300
6			Service # 5	300, 750, 1500	1,500
CALCULATION

And this how the result should look like:

Excel 2010
	B	C	D	E	F
1	Freq		Number of Services	Type of Services	Job plan
2	300		Service # 1	300	300
3	750		Service # 2	300	300
4	1,500		Service # 3	750	750
5			Service # 4	300	300
6			Service # 5	300	300
7			Service # 6	300, 750, 1500	1,500
CALCULATION

I would really appreciate if someone would take a look on it.

 

[PatOBrien198]

This may or (probably) may not have an affect -

Your Dim statement here actually sets only the last named variable as an integer. The rest are "Variant" type.

'Numbers needed as Integer
Dim i, x, NumRows, MyRows, LowestFreq, HighestFreq As Integer

To get the integer types:

Dim i as integer
dim x as integer
etc.

 

[johnny5837]

Thank you PatOBrian198, I made the changes but it did't change the results.