Forum Discussion

OwenRandy214's avatar
OwenRandy214
Copper Contributor
Mar 04, 2024

Need an excel macro/script to populate static data fields in a csv worksheet

 

 

I have a csv excel worksheet that I have to produce daily from a data file, exported from a fuel recorder box. I recorded a micro/script in excel to produce this worksheet. When completed, I have to upload the .csv file to a software program. It has to stay clean and contain all required headers and fields or it will fail. 

 

The data and cells change daily and are neveer the same. There could be 20 rows of data one day, then 34 the next day. No static values. 

 

My problem is that now I have 3 new static fields that I need to populate and I cant figure out how to edit my macro/script to do this. I have added the headers for the 3 static fields ( State, Location, City) to my micro but cant figure out how to populate them correctly, because the amount of rows change every day. 

 

 I need a script to add to my current one that will first look at the populated cells in row C, then populate the static fields in D,E,F.   If there is no data in C, then leave D,E,F blank. 

The static data in field D should be "KY, in E should be "OWEN", in F shoulkd be "RUSSELLVILLE"

 

Thanks for your help.

Randy

Excel
Formulas and Functions
Macros and VBA
Office Scripts
  • Steve_SumProductCom's avatar
    Steve_SumProductCom
    Iron Contributor
    Mar 04, 2024

    OwenRandy214 

    You can set the values in the ranges in D, E, and F by starting at C3 and using the following statements:

     

    ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 1).Value = "KY"
    ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 2).Value = "OWEN"
    ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 3).Value = "RUSSELLVILLE"

    This assumes that from C3 to the end of the rows, there will be no blanks in column C.  If you have blanks, then this will only set the values in the top section before you hit a blank cell in column C.

     

    If you have blanks, then you can use something like this:

    ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 1).Value = "KY"
    ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 2).Value = "OWEN"
    ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 2).Value = "RUSSELLVILLE"

     

    • OwenRandy214's avatar
      OwenRandy214
      Copper Contributor
      Mar 04, 2024
      There wont be any blanks. The first macro works perfectly. I may have another question before I finish my macro.
      Thanks for your help.

      Randy
      • OwenRandy214's avatar
        OwenRandy214
        Copper Contributor
        Mar 07, 2024
        Steve,

        Hello. I need to modify/edit my macro and need your help, please.

        I have already modified it to "save" the converted workbook in the "csv" format to a specific folder. The file name remains the same each save and just overwrites.

        But here is what i really need
        1. to "save as" a specific file name to include the current date. Example "Owen-Promiles_3/6/2024" It could also contain the time, if needed.
        2. Save to a folder called "Ready to Edit" which is on my desktop.
        3. Then simply "exit" Excel

        Thanks for your help.
        Randy

Resources