If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Selecting 1 static cell and 1 variable cell and fill down

Thread starter tidusx18
Start date Dec 14, 2008
Tags

fill down last cell macro save as select cells

T

tidusx18

New Member

Dec 14, 2008

#1

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CDanny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CDanny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CDanny%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> 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.

If any samples are required, please don't hesitate to email me at dvictoriano@bellsouth.net.

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?

Click here to reveal answer

Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Sort by date Sort by votes

jimrward

Well-known Member

Dec 15, 2008

#2

here is some code that will copy down some code in columns B and C, for the number of rows contained in column A, assuming there is a title so the data runs from A2 to the end

if you want your formulae in say columns G and H then change the 1 and 2 at the end of the WITH to 6 and 7, also note that if like me you have double quotes in your formulae you have to double them up so as to let EXCEL behave

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'
'****
' Column B first, 1 column away from A, for G change , 1 to , 6 etc
'****
'
With Range("a2", Range("a" & Rows.Count - 1).End(xlUp)).Offset(, 1)
.Formula = "=IF(ISNUMBER(MATCH(A2,Late!B:B,0)),""LATE"","""")"
End With

'
'****
' Column C next
'****
'

With Range("a2", Range("a" & Rows.Count - 1).End(xlUp)).Offset(, 2)
.Formula = "=IF(ISNUMBER(MATCH(A2,Speed!B:B,0)),INDEX(Speed!$E$1:$E$500,MATCH(A2,Speed!B:B,0)),"""")"
End With

any other stuff

'
'****
' turn back on the calculations and finish
'****
'

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

activesheet.Range("B2").Select

Upvote 0

You must log in or register to reply here.

Similar threads

A

Question

After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"

Replies: 1

Views: 176

Feb 22, 2024

Ark68

A

Question

VBA, make code move across from main file to extract file

Replies: 9

Views: 1K

Oct 31, 2019

Gerald Higgins

S

Question

Type mismatch using date in InputBox

Replies: 1

Views: 1K

Nov 14, 2019

SeniorTom

S

P

Question

Automatically inserting VBA into a workbook

Replies: 1

Views: 959

Nov 15, 2019

Phillip2

P

S

Question

Macro for DATEVALUE

Replies: 3

Views: 1K

Nov 10, 2019

jasonb75

J

Share:

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,214,918

Messages: 6,122,255

Members: 449,075

Latest member: staticfluids

Share this page

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Top