Selecting 1 static cell and 1 variable cell and fill down

[tidusx18]

Hello,

I have GPS data in an excel sheet (CSV) that I download from my GPS data logger. I format this data and use IF/THEN formulas to calculate lengths of time. I already have a macro set up that puts these formulas in the first couple rows, to the right of the columns that the GPS data is in, then i manually "fill down" the formulas until the last row w/ GPS data), but would like some VB code that I can add to my macro to automate the whole thing.

Also since the data from the logger can only export as a CSV file, I need some code that can save the file as another file type (i.e.excel workbook, PDF etc...) in the same folder that the original CSV file was opened from. (i.e. if I open Data.csv from C:\example, then it would also save to C:\example).

Please note that the name of the CSV file downloaded from the GPS device, as well as the folder its exported to will change every time.

I have attached sample files for your reference. ***I forgot to attach the file...please see the 3rd post below...its there...***

Let me know if you need any additional info and thank a lot in advance.

[MaximS]

there is no files attached

[tidusx18]

there is no files attached

Sorry about that...here it is...

[MaximS]

Use that code to fill the formulas regardless number of rows you have:


Sub Formula_Filler()

Dim LRow As Long

LRow = Range("A65536").End(xlUp).Row

Range("H2").Formula = "=B2+C2"
Range("H2").Copy Destination:=Range("H3:H" & LRow)
Range("H2:H" & LRow).Formula = Range("H2:H" & LRow).Value

Range("I3").Formula = "=IF(AND(H3-H2<TIME(0,0,3),G3>1, G3<7),H3-H2,0)"
Range("I3").Copy Destination:=Range("I4:I" & LRow)
Range("I3:I" & LRow).Formula = Range("I3:I" & LRow).Value

Range("J3").Formula = "=IF(AND(H3-H2<TIME(0,0,3),G3<1),H3-H2,0)"
Range("J3").Copy Destination:=Range("J4:J" & LRow)
Range("J3:J" & LRow).Formula = Range("J3:J" & LRow).Value

Range("K3").Formula = "=IF(AND(H3-H2<TIME(0,0,3),G3>7),H3-H2,0)"
Range("K3").Copy Destination:=Range("K4:K" & LRow)
Range("K3:K" & LRow).Formula = Range("K3:K" & LRow).Value

Range("L3").Formula = "=IF(H3-H2>TIME(0,0,3),H3-H2,0)"
Range("L3").Copy Destination:=Range("L4:L" & LRow)
Range("L3:L" & LRow).Formula = Range("L3:L" & LRow).Value

End Sub



If it's about automatic opening and saving I need more details about source file name and destination folder (format). How do that names changing?

[tidusx18]

Thanks for that code...works great. Only thing is I can't see the formulas once everything is in the file and would like to be able to just in case I need to change something on that particular file.

As far as how the file names and destination file names change...each time a worker turns in one of the GPS devices, I download the data, save it with their name (many different workers come and go so there are many dif names). Each worker does work for different clients of ours each time, so each excel file is saved in a different folder on the computer (relative to the name of the client which the worker was doing the work for).

Thats why it would be ideal if the VB code could save the excel CSV file as another file type, in the same folder that it was opened from.

Also, I would like to note that I open each excel CSV file manually and then use the macro.

If you have any other suggestions or need any more info, please let me know. thanks a lot! :)

[MaximS]

Remove them parts to keep formulas
Range("H2:H" & LRow).Formula = Range("H2:H" & LRow).Value

Range("I3:I" & LRow).Formula = Range("I3:I" & LRow).Value

Range("J3:J" & LRow).Formula = Range("J3:J" & LRow).Value

Range("K3:K" & LRow).Formula = Range("K3:K" & LRow).Value

Range("L3:L" & LRow).Formula = Range("L3:L" & LRow).Value


can you please write sample folder and file names then we will see what we can do about it?

[tidusx18]

Sure...see below...

One of the folders where the files are saved:
"C:\Clients\Alex" (Other folders for different clients would be "C:\Clients\"client's name here"")

A name of an excel CSV file could be "GPS data 01.csv" (this would be saved or exported from the GPS device, then opened manually by me and then I run the macro to format the file)

I only need the code to save whatever file I open, whether it be "GPS data 01.csv or "GPS data 02.csv" back in the folder that I opened it from. In the example I listed above, that folder would be "C:\Clients\Alex".

I am actually going to sleep for the night so if you need anything else just post or email me at "dvictoriano@bellsouth.net" and I'll reply tomorrow.

thanks again for all your help. It is very much appreciated and needed.