[VBA] Copying data from Excel to .txt File

Hello

I am trying to make VBA to copy data from Excel to .txt file.
The problem is that I am not quite sure of 2 things

1) How to define the range so that VBA should know to take a specific range for each Car make (for the first Car make the range was initiated manually). So I think the condition would be something like: If(Car make==same as Car make from rows below), then do a minimum on Mileage for that range, and this should be done for each Car make.
.
2) How to make the code more efficient rather than declaring a lot of variables. As there would be hundreds of entries (multiple Car makes, and after each make the lowest Mileage should be displayed). As what has been done so far for rows 2-3 should also be done for rows 4-7, and so on... But for the sake of the example I kept it short.


Below you'll find attached the Workbook (the code is located in Module1), as well as the expected Answers in the .txt file.

I would be more than grateful if someone could help me out with it.

Many thanks, I appreciate it!

Gordon

I've also attached the

Please Login or Register  to view this content.

The answers should be:

AUTO
CAR MAKE&MODEL:Audi A4
01.01.2000 Date + Rent & Return Days : MondayFriday
CAR MAKE&MODEL:Audi A4
11.04.2000 Date + Rent & Return Days : TuesdayFriday
Lowest Mileage is: 1200
CAR MAKE&MODEL:BMW 320D
05.05.2010 Date + Rent & Return Days : MondaySunday
CAR MAKE&MODEL:BMW 320D
02.01.2010 Date + Rent & Return Days : ThursdaySaturday
CAR MAKE&MODEL:BMW 320D
01.01.2009 Date + Rent & Return Days : FridaySunday
CAR MAKE&MODEL:BMW 320D
05.08.2012 Date + Rent & Return Days : MondayWednesday
Lowest Mileage is: 1532

Here is how the Excel looks like:
Capture.JPG
Please note that there can be hundreds of entries, this example has few entries.

Hi Gordon85,
I came up with this solution, but only 1 condition has to be met: column A, the cars, has to be sorted (alfabetically):

Please Login or Register  to view this content.

Cheers
Erwin

Hello Erwin,

That works like a charm (Rep+). The only problem would be that the Code is targeting specific columns, can this be tweaked to look for specific keywords as I tried above in post #2?

I am asking because there will be more than 1 Excel document, and the names of the column headers might vary, that's why I used the search function for specific keywords combined with Active Sheet, because I am trying to run the VBA Code from an External .xlsm file to target different .xlsx files.

Thank you!
Gordon

Sure, look, everything in Cells can be changed, as it is defined as Cells(RowNum, Colnum), and I have fixed the latter.
That can be changed of course
I think you know enough VBA to do it yourself, otherwise we can help you

Cheers
Erwin

Thank you as well, interesting approach! Rep+

By the way, would there also be a way to get the Date where the minimum Mileage appears and put it on the same line with the Lowest Mileage?

Sort of like:

Date associated with lowest Mileage, "Lowest mileage is", Lowest Mileage value

I tried putting " " between the Car make, but silly me got it wrong I guess

Please Login or Register  to view this content.

Should have outputted :Car Make & Model:"Audi A4" for example, any idea why it doesen't work?

Thank you!
Gordon

According to the initial attachment and posts #1, 4 & 7 an easy VBA starter demonstration (edit v3) :

PHP Code: 

Sub Demo1()
      Const S = " Lowest Mileage is :"
        Dim V, C, H, W, F%, R&
            V = [{"Car*","Date*","Rent*","Return*","Mile*"}]
    With Sheet1.UsedRange.Rows
            C = Application.Match(V, .Item(1), 0):  If Application.Count(C) < UBound(V) Then Beep: Exit Sub
           .Sort .Cells(C(1)), xlAscending, Header:=xlYes
            H = Array(.Cells(C(1)) & " : """, """" & vbNewLine)
            W = Application.Index(.Item(2), , C)
            F = FreeFile
            Open ThisWorkbook.Path & Application.PathSeparator & "Answers .txt" For Output As #F
            Print #F, "AUTO"
        For R = 2 To .Count
            V = Application.Index(.Item(R), , C)
            If V(1) <> W(1) Then Print #F, W(2); S; W(5): W = V Else If V(5) < W(5) Then W = V
            Print #F, H(0); V(1); H(1); V(2); " Date + Rent & Return Days : "; V(3); " "; V(4)
        Next
    End With
            Print #F, W(2); S; W(5)
            Close #F
End Sub 


New edit in post #9 :

• Resolves the sort issue if the car model is not the first column …
  
  
• May increase the speed for hundreds / thousands source rows.
  As the more rows, the slower Index worksheet function.
  So it's faster to use Index with a row at a time - like to not play with huge strings - and the code is a bit simplified …

Guys, would be there any other way when copying the data from the Excel to the .txt file, to have just 1 appearance of the Car name and introduce another keyword such as color where the date would also be printed?

For example if there is something like this in the Excel file
Capture.JPG



To output something like this?

Car make & model "BMW 320D"
05.05.2010 Rent+Return Monday Sunday
02.01.2010 Rent+Return Thursday Saturday
01.01.2009 Rent+Return Friday Sunday
01.01.2009 Lowest mileage is 1532
05.05.2010 Color Red
02.01.2010 Color Green
01.01.2009 Color Blue
Car make & model "Audi A4"
02.01.2020 Rent+Return Wednesday Saturday
04.02.2011 Rent+Return Monday Thursday
02.01.2020 Lowest mileage is 2312
02.01.2020 Color Yellow
04.02.2011 Color Pink
Car make & model "Audi A5"
02.03.2008 Rent+Return Friday Saturday
02.03.2008 Lowest mileage is 123124
02.03.2008 Color Orange


Many thanks!
Gordon

With such weird text export format maybe I will wait until the rules will be definitively fixed according to a new attachment
as it's the third time you modify them …