Posts by Caleeco

Caleeco · Jun 1st 2016

Re: Macro to save selection as .csv Run-time error '1004'

Quote from luck_excel;771773

Hey Calecco,

Thanks for your suggestion, I tried it already and tried again I'm running on Mac Office 2016 if that makes any difference. Like you I thought the path would be using the \ but it seems that regardless / or \ I get the same error message.

ahh.... with Mac Office... the filepath structure will be different because it is UNIX. From the limited experience I have with MAC.. i dont believe the / or \ is needed at all... so just remove it. Failing that, have a read of this guide, maybe it'll provide some clues!
http://www.rondebruin.nl/mac/mac010.htm

Caleeco

Caleeco · Jun 1st 2016

Re: Macro to save selection as .csv Run-time error '1004'

This is untested, but i think switching the separator from "/" to "" may solve your problem?

Code

ActiveWorkbook.SaveAs MDir & "\" &

Let me know if it works
Caleeco

Caleeco · May 31st 2016

Re: UDF Debug?

Hmm strange.. is is possible because your Dim'd them as Integer and 50,600 is outside the limit of what an integer can hold? (Although I though Excel upgraded these to long anyway). Try this instead

Code

Public Function MAQCheck(Classification, NoCab, InCab, Sprinklers, Units)
Dim MAQ As Long
Dim x As Integer
Dim totalV As Single
Dim MAQL As Long
Dim MAQG As Long
     
If Sprinklers = "YES" Then
    x = 2
ElseIf Sprinklers = "NO" Then
    x = 1
End If
 
If Classification = "CLASS IA" Then
    MAQG = 30
    MAQL = 115
ElseIf Classification = "CLASSES IB & IC" Then
    MAQG = 120
    MAQL = 460




ElseIf Classification = "CLASSES IA, IB & IC" Then
    MAQG = 120
    MAQL = 460




ElseIf Classification = "CLASS II" Then
    MAQG = 120
    MAQL = 460




ElseIf Classification = "CLASS IIIA" Then
    MAQG = 330
    MAQL = 1265




ElseIf Classification = "CLASS IIIB" Then
    MAQG = 13200
    MAQL = 50600
Else
    MAQCheck = "Class not found"




End If




MAQG = MAQG * x
MAQL = MAQL * x
 
If Units = "GALLONS" Then
    If MAQG >= totalV Then
        MAQCheck = "YES"
    Else
        MAQCheck = "NO"
    End If
ElseIf MAQL >= totalV Then
        MAQCheck = "YES"
    Else
        MAQCheck = "NO"
     'End If dont think we need this IF?
End If
  
End Function

Display More

Caleeco

Caleeco · May 31st 2016

Re: UDF Debug?

I assume that there is only 1 Classification output per use, so would a normal If, ELSEIF, ELSE function not work better?

Code

If Classification = "CLASS IA" Then
    MAQG = 30
    MAQL = 115
ElseIf Classification = "CLASSES IB & IC" Then
    MAQG = 120
    MAQL = 460
             
ElseIf Classification = "CLASSES IA, IB & IC" Then
    MAQG = 120
    MAQL = 460
                 
ElseIf Classification = "CLASS II" Then
    MAQG = 120
    MAQL = 460
                     
ElseIf Classification = "CLASS IIIA" Then
    MAQG = 330
    MAQL = 1265
                         
ElseIf Classification = "CLASS IIIB" Then
    MAQG = 13200
    MAQL = 50600
Else
    MAQCheck = "Class not found"
                             
End If

Display More

Caleeco · May 31st 2016

Re: code rectification needed

Hello Libin,

Why dont you give this code a go:

Code

Sub Right1()
Dim sSplit() As String
Dim data As Range
Dim c As Range
Set data = ActiveSheet.Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In data
    sSplit = Split(c.Value, "-")
    ' c = sSplit(0) 'uncomment this line if you want the starting 3 letters only in column A
    c.Offset(0, 1) = sSplit(1)
    c.Offset(0, 2) = sSplit(2)
Next c
End Sub

Display More

Let me know if it works for you
Many Thanks

Caleeco

Caleeco · May 31st 2016

Re: Finding the earliest date w/ matching criteria

Hello SkyHigh,

I assume that was a typo in your post, and the resut should be U2011 - 01/18/2016?

If so, give this macro a try. NOTE: please change your the sheet name and column to reference. Currently the macro is looking at Sheet3, and assumes 'Project' Data starts in cell B2.

Code

Sub EarliestSchStart()
Dim data As Range
Dim c As Range
Dim NewDate As Date
Dim OldDate As Date
'Change your Sheet Name and range of "Project" on the next line
Set data = Worksheets("Sheet3").Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each c In data
    Debug.Print c.Value
    If Left(c.Value, 5) = "U2011" Then
        NewDate = c.Offset(0, 1).Value
        If OldDate = Empty Then
            OldDate = NewDate
        ElseIf NewDate < OldDate Then
            OldDate = NewDate
        End If
    End If
Next c


MsgBox "The earliest Scheduled Start Date is:  " & "U2011 - " & OldDate
End Sub

Display More

Let me know how you get on
Thanks

Caleeco

Caleeco · May 31st 2016

Re: vba sort by date

Hello Pepe,

When you say, it is not working correctly. What is the problem? is it throwing up any error messages? is it not sorting dates sequentially?

The code below is a structure you can copy. It will sort on column E (dates).

However, it assumes that there is data ajacent to the Dates column E. So the range I have set is for columns C:E (change this as you please). The other thing to check would be that the cells are all formatted as dates and not text.

Code

Sub SortData()
Dim rData As Range
Dim rDates As Range
Dim lr As Long
With ActiveWorkbook.Worksheets("Updates")
    .Activate
    .ShowAllData
    lr = .Cells(Rows.Count, "E").End(xlUp).Row
    Set rData = .Range("C2", Range("E" & lr))
    Set rDates = .Range("E2", Range("E" & lr))
End With
rData.Sort key1:=rDates, order1:=xlAscending, Header:=xlNo
End Sub

Display More

Let me know how you get on.

Many Thanks
Caleeco

Caleeco · May 31st 2016

Re: VBA - Add new row based on value and copy adjecent row and paste in the new row

Hello Behedwin,

Try out the code below. Bare in mind you will need to change the "Keyword" and sheetname with these two lines:

Code

Keyword = "Keyword"With Worksheets("Sheet1").Columns("A:A")

which appear in the full code below

Code

Sub FindFirstInstance()
Dim rFound As Range
Dim Keyword As String
'Set your keyword here
Keyword = "Keyword"
With Worksheets("Sheet1").Columns("A:A")
    Set rFound = .Find(What:=Keyword, After:=Range("A" & Rows.Count), LookIn:=xlValues, _
        lookat:=xlWhole, Searchorder:=xlByRows, Searchdirection:=xlNext, MatchCase:=False)
    
    If Not rFound Is Nothing Then 'Value has been found
        rFound.EntireRow.Insert
        rFound.Offset(-2, 0).EntireRow.Copy
        
        With rFound.Offset(-1, 0)
            .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
            .EntireRow.SpecialCells(xlConstants).Clear
        End With
        
    Else 'Value has NOT been found
      Exit Sub
    End If
End With
End Sub

Display More

This assumes there is only 1 instance of the keyword in column A. However, it can be adapted to find multiple instances.

Let me know if it works.
Thanks
Caleeco