How to add range in a Formula through VBA

[jhabijay_10]

Dear All,

I have attached a sales tracker ,every month i have to add a range to get the the min and max value.
Now i want to automate the same min and max formula with the help of Vba .
Can any one please help me .

Dim InptboxName As String
Rows("1:1").Select
colno = Selection.Find("Min").Column
Cells(1, colno).EntireColumn.Insert
Cells(1, colno).Offset(2, 0).Select
InptboxName = InputBox("Enter the Name of Month") 'December
ActiveCell.Value = InptboxName
ActiveCell.Offset(1, 1).Select
' Automate the formula of min and max.

[Richard Buttrey]

Hi,

VBA is not needed. You could use standard Excel functions.
Here's one array formula - (enter with Ctrl-Shift-Enter) in AJ4 and copy down
Put the letter Q in row 1 above every Quarter column so that the formula can ignore those columns

=MAX(IF(($C4:$AK4<>"n/a"),IF(C$1:AK$1<>"Q",$C4:$AK4,0),0))

Change MAX to MIN for the minimum value.

Personally I'd be inclined to capture your data in a normalised 2 dimensional table of column field headers and rows for every record that could be used by a pivot table, or by simpler formulae in any pre-formatted Sales Report.
i.e. Have a separate sheet in which you have columns for recording:

Date
TP Name *
Product Name *
Quantity

The * fields could be drop down validation cells that contain a list of the allowable choices like say "TV", "Furniture"..etc to make Data entry even simpler.

[jhabijay_10]

Thanks ,

But i would like to do in VBA only , as only a part of data i have shared .
There were several tasks i have to perform on the same sales tracker.
So Please suggest me if it would be done through VBA.

Thanks

[Richard Buttrey]

Thanks ,

But i would like to do in VBA only , as only a part of data i have shared .
There were several tasks i have to perform on the same sales tracker.
So Please suggest me if it would be done through VBA.

Thanks

Why not apply the formula I gave you USING VBA if you don't want to add it manually?