Time for another summary of my Excel posts. Best way to repeat the information is to make a little project with modules for import, export and some operations on values.

At the beginning I would like to create a folder, perhaps on my Desktop with source file already inside this path.

First module will be called mImportExport with follwoing procedures:

Sub ImportCSVFile()

Already I have presented a few ways how to import files with various extension as well in Excel as in Access environment.

Sub SaveReportAS()

Dim FilePath As Office.FileDialog
Dim Answer As VbMsgBoxResult
Dim FileName As String

Sub SaveMethod1()
'open csv file and read it name
Set FilePath = Application.FileDialog(msoFileDialogSaveAs)

With FilePath
.InitialFileName = ThisWorkbook.Path
.AllowMultiSelect = False
.Title = "Save current worksheet as: "

If .Show = True Then
ActiveWorkbook.Save
ThisWorkbook.SaveCopyAs _
"C:\Users\Agnieszka\Desktop\PROJECT\Offer" & FileRec & ".txt"
End If
End With

'MsgBox ThisWorkbook.Path
End Sub

Sub SaveMethod2()

Dim s As Variant

s = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path, _
fileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save current file: ")

If s <> False Then
ActiveWorkbook.Save
ThisWorkbook.SaveCopyAs _
"C:\Users\Agnieszka\Desktop\PROJECT\Offer" & FileRec & ".txt"
End If

End Sub

Some functions, like special file mark:

Public Function FileRec() As String
FileRec = Format(Now, “_yyyy-mm-dd_hh-mm-ss”) & “_fileauthor_” & Application.UserName
End Function

See whole code here: mImportExport

Second module gonna be about operations we would like to do in Excel after source data implementation.

What I would like to count is:

tax values based on const. rate and sale price.

‘tax value
ActiveSheet.Cells(x, 12).Value = Round(CDbl(ActiveSheet.Cells(x, 10).Value) + _
CDbl(ActiveSheet.Cells(x, 10).Value * 0.23), 1)

and Total Sale Amount including gross price and on_stock quantity.

‘total
ActiveSheet.Cells(x, 13).Value = Round(CDbl(ActiveSheet.Cells(x, 12)) * _
CDbl(ActiveSheet.Cells(x, 9).Value), 1)

Also I would like to have a botton in my project, with which I could restore data before described above operations (tax & total sale).

Sample of these codes you can see by openinig this file with module:

mOperates

Enough for this post. With second part of project we will write macros to enables stats presentations and read data from web sites.

See ya!

Today I will show you, how can we count data according specific criteria and How can we sum values regarding chosen condition.

1. Count By Font Color

Function CountByFColor(range_data As Range, criteria As Range) As Long
Dim xData As Range
Dim xcolor As Long
xcolor = criteria.Font.Color

For Each xData In range_data
If xData.Font.Color = xcolor Then
CountByFColor = CountByFColor + 1
End If
Next xData
End Function

2. Count By Interior Index Color

Function CountByRcolor(range_data As Range, criteria As Range) As Long
Dim xData As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex

For Each xData In range_data
If xData.Interior.ColorIndex = xcolor Then
CountByRcolor= CountByRcolor+ 1
End If
Next xData
End Function

3. Count By Value

Function CountByValue(range_data As Range, criteria As Range) As Long
Dim xData As Range
Dim xValue As String
xValue = criteria.Value

For Each xData In range_data
If xData.Value = xValue Then
CountByValue = CountByValue + 1
End If
Next xData
End Function

Check result

Enter following functions under CountFruitCategories:

=CountByFColor(A2:A15;A2)
=CountByRcolor(A2:A15;A2)
=CountByValue(A2:A15;A2)

Sum by criteria

Let’s say we would like to sum Prices and Amount reagrding Fruit Types. Write a code in VBA:

Range("H3").Value = WorksheetFunction.SumIf(Columns(1), "Apple", Columns(2))
Range("H4").Value = WorksheetFunction.SumIf(Columns(1), "Pear", Columns(2))
Range("H5").Value = WorksheetFunction.SumIf(Columns(1), "Banana", Columns(2))
Range("H6").Value = WorksheetFunction.SumIf(Columns(1), "Orange", Columns(2))

We can also declare an one-dimensional table, like this:

Dim Frts(3) as String
Frts(3) = “Apple”
Frts(2) = “Pear”
Frts(1) = “Banana”
Frts(0) = “Orange”

Range(“E3”).Value = WorksheetFunction.SumIf(Columns(1), Frts(3), Columns(2))
Range(“E4”).Value = WorksheetFunction.SumIf(Columns(1), Frts(2), Columns(2))
….

result