beginner VBA user. How do I find the max in this column?

[Jwan622]

1) I have a column from a20:a27 with numbers from 1-8. I'm trying to find the max of the number using a simple loop. How do I do this? So far I have:
Sub find_max1()
Dim x As Integer
for X = 1 to 7000
cells(x,1).max
next X
End Sub



2) Once I obtain that max value, I want to put it in row 1. After that I want to take the max value - 1 and put it in row 2, etc, until I have put the last number in the last row. How do I do this

 

[njimack]

Welcome to the board. Give this a whirl...

Sub Max()


Application.ScreenUpdating = False


Dim i As Long
For i = 1 To 8
    Cells(i, 1) = WorksheetFunction.Large(Range("A20:A28"), i)
Next i


Application.ScreenUpdating = True


End Sub

 

[Jwan622]

So what I eventually did was this:

Dim max As Integer
max = 1
For row = 20 To 100
If Cells(row, 1) > max Then
max = Cells(row, 1)
End If
Next row
Cells(102, 1).Value = max
End Sub




Neil's code also makes sense... I'm just wondering what all of the "application.screenupdating" and "worksheet.function..." commands are used for??? Anyone got an intuitive answer for why this is the case?

Welcome to the board. Give this a whirl...

Sub Max()


Application.ScreenUpdating = False


Dim i As Long
For i = 1 To 8
    Cells(i, 1) = WorksheetFunction.Large(Range("A20:A28"), i)
Next i


Application.ScreenUpdating = True


End Sub

 

[njimack]

Application.ScreenUpdating stops the user interface from updating while the macro is running. This is good programming practise because it stops the screen flickering, and speeds up the code.

Worksheetfunction.Large is the VBA method for calling the LARGE function.

 

[Jwan622]

What does the large function do????

 

[Jwan622]

I answered my lazy question myself about large.

The application screenupdating feature is quite cool... I've never heard of that before. Is that difference noticeable???

Also, how do you save macros? Do you need to have your personal xlsb book open everytime to access saved macros????

 

[njimack]

I answered my lazy question myself about large.

The application screenupdating feature is quite cool... I've never heard of that before. Is that difference noticeable???

Also, how do you save macros? Do you need to have your personal xlsb book open everytime to access saved macros????

The difference that screenupdating makes depends on what your code does. If you're opening workbooks, refreshing pivot tables, or writing formulae to cells then the performance difference can be substantial, as well as stopping the screen from flickering. As a matter of course, most code I write looks something like this...

Sub a()
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With
'code goes here
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .Calculation = xlAutomatic
End With

End Sub

TO save a macro, it depends where you've written the code. If it's in a module in a workbook, then the code will be saved to that workbook, but will only be available when that workbook is open. If you want the code available at all times, save it to your Personal.xls file (which can be hidden).