Posts by Brian Walters

Hopefully this approach works for you. I referenced the exact rows and columns you're using, not anticipating changes to the format of the sheet.

I've added a macro, but haven't thoroughly checked the results.

Re: Need to divide a number between 1 and 3 depending on scenario

See if the attached file works for you.

Re: code works great for min/max to get usage, except when we fill a tank

Actually you need to address all of the WhatDate.Offset parts of the code. So WhatDate.Offset(-1,0) is offseting 1 row up, but you will want 1 column to the right, so you will want WhatDate.Offset(0,-1). WhatDate.Offset(0, 4).Value is supposed to reference the current column's fuel level, so you will want WhatDate.Offset( # of rows down from dates row, 0) and instead of WhatDate.Offset(-1, 4).Value, you will want WhatDate.Offset( # of rows down from dates row, -1)

Re: code works great for min/max to get usage, except when we fill a tank

In cell A1 you need to type in 2013.
This: Set WhatDate = Sheets("YTD Fuel Cost").Range("A1") should be this: Set WhatDate = Sheets("Veeder Root Report Archive").Range("I1")
And since your dates go across columns of row 1 rather than down rows, change this: Set WhatDate = WhatDate.Offset(1, 0) to this: Set WhatDate = WhatDate.Offset(0, 1)
Let me know if these changes work.

Re: Sliding scale calculation

If cell A1 holds the 3200 value then:
=MIN(A1,500)*0.9654+IF(A1>500,(MIN(A1,1000)-500)*0.9905,0)+IF(A1>100,(MIN(A1,2000)-1000)*1.0113,0)+IF(A1>2000,(MIN(A1,3000)-2000)*1.0354,0)+IF(A1>3000,(A1-3000)*1.0476,0)

Re: code works great for min/max to get usage, except when we fill a tank

See if you can modify the macro in the attached file.

Re: Folder Save

Here is a way to check to see if folders exist and if not, create them. There may be a more efficient way, but this will show you how to make folders.

Sub MakeFolders()
Dim fs, objFolder
Dim MyFile As String


  Set fs = CreateObject("Scripting.FileSystemObject")
  
  MyFile = Dir("C:\Test", vbDirectory)
  If MyFile = "" Then
    Set objFolder = fs.CreateFolder("C:\Test")
  End If
  
  MyFile = Dir("C:\Test\Ozgrid", vbDirectory)
  If MyFile = "" Then
    Set objFolder = fs.CreateFolder("C:\Test\Ozgrid")
  End If
  
  MyFile = Dir("C:\Test\Ozgrid\Joey", vbDirectory)
  If MyFile = "" Then
    Set objFolder = fs.CreateFolder("C:\Test\Ozgrid\Joey")
  End If
  
  MyFile = Dir("C:\Test\Ozgrid\Joey\Desktop", vbDirectory)
  If MyFile = "" Then
    Set objFolder = fs.CreateFolder("C:\Test\Ozgrid\Joey\Desktop")
  End If


End Sub

Re: Matching dates and then Using values

You could try this:

Sub Test()
Dim WhatDate As Range


Set WhatDate = Sheet1.Cells(4, 3)  'This sets the variable WhatDate to cell C4 on Sheet1


Do Until Len(WhatDate) = 0  'This will loop until the first empty cell
  WhatDate.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R4C4:R1003C5,2,FALSE)"
  Calculate
  WhatDate.Offset(0, 1).Value = WhatDate.Offset(0, 1).Value
  Set WhatDate = WhatDate.Offset(1, 0)
Loop


End Sub

Re: code works great for min/max to get usage, except when we fill a tank

Could you use a SumIf?
Like: =SUMIF(I1:GZ1,"<"&DATE(Year(B2)+1,1,1),I65:GZ65)-SUMIF(I1:GZ1,"<"&DATE(Year(B2),1,1),I65:GZ65)

Re: transfer data with commandbutton

See if you can modify the macro in the attached file to suit your needs.

Re: VBA: Extract Everything to the Left

Could you post the example of the array? Is there a way to loop through the array and act on each string?

For example, if you have a variable WhatStrings() and it has values "Ford F150 Operation", "Samsung F250 Operation", ...
Can you put in a loop such as

For i = 1 to 10 'or whatever the limit or number of strings is
  Range("B" & i).Value = Application.WorksheetFunction.Replace(WhatString(i), Application.WorksheetFunction.Search(" Operation", WhatString(i)), Len(" Operation"), "") 
Next i

Re: Macro for Summary Sheet

Let me know if you want additional changes.

Re: Macro for Summary Sheet

We can get it to do almost anything you want. The attachment didn't work for some reason so I can't see what the example is. Can you upload a file that has the example? Or you can describe what columns you want copied and other changes you may want.

Re: VBA: Extract Everything to the Left

I apologize, I didn't read that this was in VBA. See if you can modify this code:

Public Sub TestIt()
Dim i As Long


For i = 1 To 2
  Range("B" & i).Value = Application.WorksheetFunction.Replace(Range("A" & i), Application.WorksheetFunction.Search(" Operation", Range("A" & i)), Len(" Operation"), "")
Next i


End Sub

I simply put Ford F150 Operation in cell A1 and Samsung F250 Operation in cell A2 and the macro puts the text without " Operation" in column B.

Re: Dynamic print area

Try this:

Range(Range("A" & firstbcity.Row),Range("AB" & firstbcity.Row))

Re: Macro for Summary Sheet

You should be able to expand on the attached file or copy the macro into your file.

Re: VBA: Extract Everything to the Left

Is it always something and " Operation"?

If so, use this: =REPLACE(A1,SEARCH(" Operation",A1),LEN(" Operation"),"")

Re: Create Running Budget Formula

See if the attached file works for you.

Re: Select an Excel sheet with 2 variables in it?

I think you just want this:
Sheets(Cstr(y) & " - " & Cstr(i) & " load").Select