Excel help

[ATW Alex 101]

Hi guys,

I have been working on an excel spreadsheet that I will use to record my rail mileage and haulages etc. It will also include statistics and had the ability to record all loco haulages, aircraft haulages and log railtours.

It's nearing completion but I have stumbled across a problem. If somebody would be able to help me that would be great. It can be accessed in the attachment below.

If you open it up and go into the sheet named 'Loco_haulage', I want to make it so that if I enter 'LOCO' into the TOPS range in the sheet titled, 'Railmiles_log', that it will enter all the relevant data (from, to, headcode etc.) into the relevant cells. I want to make it so that however far down in the list in the 'railmiles_log' that 'LOCO' appears, the relevant data will appear in the next available row in the 'Loco_haulage'. I have tried using =IF, but if say I enter 'LOCO' in the 18th row down on the TOPS column on the 'Railmiles_log', the data will appear on the 18th row in the 'Loco_haulage' sheet, rather than the first which is what I want it to do. Then for the next 'LOCO' entry, make the data appear in the next row down. I have done =IF in the first row of 'Loco_haulage' to show what I am trying to do.

Any help or relevant formulae will be much appreciated. I know this may be a little complicated as well and apologize for that. Any questions, just ask.

Cheers in advance.

 

[table38]

I reckon you want a bit of VBA. You can use Sheets("Loco_Haulage").UsedRange.Rows.Count to find the next free line in that sheet, but I'd be inclined to clear out the Loco_Haulage sheet first then rebuild it each time you run the macro.

Unless someone else has a better idea! I know you can do a vlookup but I'm not sure how you would persuade it to use the next free row.

---- Edit ----

Had a quick go, not very elegant! Click on the Run button in the Railmiles_log sheet to populate the loco_haulage sheet

 

[ATW Alex 101]

Table, thanks for the reply. I don't quite understand how macros and that work. Is it possible you could explain a little more about how your method works? I am quite liking it and it could be the solution to the problem I am trying to overcome.

 

[table38]

OK Excel macros are essentially "Visual Basic" code. The first thing you want to do is to enable the Developer tab in excel (File -> Options -> Customize Ribbon and check the Developer in the right pane)

Then click on Developer in the main ribbon, then Visual Basic. A new window will open; you might need to expand the project and modules, then double click on Module1 and the VBA script will appear. That's the code that is actually doing the work. (The "Run" button in the Railmiles_log sheet is just set up to run that "macro" to make life a bit easier).

Sub Main()

  Dim y, n
  
  n = 2
  
  For y = 3 To Sheets("Railmiles_log").UsedRange.Rows.Count
    If Trim(UCase(Sheets("Railmiles_log").Range("G" + CStr(y)).Value)) = "LOCO" Then
      n = n + 1
      Sheets("Loco_haulage").Range("A" + CStr(n)).Value = Sheets("Railmiles_log").Range("A" + CStr(y)).Value
      Sheets("Loco_haulage").Range("B" + CStr(n)).Value = Sheets("Railmiles_log").Range("B" + CStr(y)).Value
      Sheets("Loco_haulage").Range("C" + CStr(n)).Value = Sheets("Railmiles_log").Range("C" + CStr(y)).Value
      Sheets("Loco_haulage").Range("D" + CStr(n)).Value = Sheets("Railmiles_log").Range("H" + CStr(y)).Value
      Sheets("Loco_haulage").Range("E" + CStr(n)).Value = Sheets("Railmiles_log").Range("F" + CStr(y)).Value
      Sheets("Loco_haulage").Range("F" + CStr(n)).Value = Sheets("Railmiles_log").Range("I" + CStr(y)).Value
      Sheets("Loco_haulage").Range("G" + CStr(n)).Value = Sheets("Railmiles_log").Range("J" + CStr(y)).Value
    
    End If
  
  Next

End Sub

All the code does is to look in all the rows in the Railmiles_log sheet, and for each row, checks the value in column G (the trim and ucase just remove any leading or trailing spaces and changes it to upper case) and if it is, it sets the values of the cells in the Loco_haulage sheet to the value of the cell in the Railmiles_log sheet.

The terms "VBA" and "macro" are sort of used interchanably in Excel, because if you record a macro, it saves it as VBA. You can try it yourself - try "Record Macro" in the developer tab, then maybe change a cell value or font or something then do "Stop Recording" then click on "Macro" and take a look at the code you just created.

Lots more on VBA at http://www.w3schools.com/vbscript/default.asp

Hope that'll point you in the right direction, feel free to get back to me if you need any more help.

 

[ATW Alex 101]

I understand a little more, but it's still way over my head. I'm not really used to macros and visual basic. I thank you though for your help though. Is there any way I can do what I want to do using formulae?

 

[table38]

I was wondering last night if you could do it the way you do now, but have some sort of autofilter to remove the blank lines from the loco_haulage sheet?

So every line in loco_haulage would pull the data over from the corresponding line in railmiles_log, then in loco_haulage, do Data -> Filter and use one of the drop-downs in the column headers to filter away the blank lines?