Posts by Luke M

Re: To get all the rows in an excel sheet where a specific column has a given value.

Can you just use the Filter function? Either filter in place using AutoFilter, or setup an AdvanceFilter? http://www.contextures.com/xladvfilter01.html

Re: Icon Sets in Pivot Table with Condition

I would setup a formula in a cell somewhere that calculates the numerical values. Then, in your CF rule, you can set the Icon set to reference the cell(s).

Re: Dealing with Captcha in VBA

If the website has a captcha, usually that's an effort to stop automated processes from using their site. Many websites do that actually, as part of their Terms and Conditions. As such, I would first double check to make sure you're not breaking any rules or laws. It's probably low key, but just want to state for the record.

For the code, can you just add in a basic wait, something like

MsgBox ""You have 15 seconds to enter the code!"
Application.Wait Now + TimeValue("0:00:15")
'User should fill in captca during this time


'Continue with code

Re: Excel table is inserting old formula in new rows. How can I reset the saved formu

Go to a cell with a formula you desired. Edit the cell, then press Enter to re-confirm it. You should see a small tool-tip appear. You can use this to apply formula to entire column, which will "reset" the value for new rows.

Re: Pause a Macro to allow user to Name a Range

Ok. We cna just change the cell's value then

Sub ExampleCode() 
    Dim newName As String 
     
     'Some code here
     
    newName = InputBox("What is the name of the range?") 
     
    ActiveCell.Name = newName 
ActiveCell.Value = newName
     
     'Do other stuff here
     
End Sub

Re: Pause a Macro to allow user to Name a Range

I'm not sure what you mean. Did the above solve things, or not. Naming a cell/range is fairly straightforward. Was I correct in assuming you were using Named Ranges?

Re: Deleting rows by value in a given column or selection

Oops, I forgot a line. In the loop, need to tell code to find the next cell.

Sub MainCode()
    Application.ScreenUpdating = False
    Call KillThis("A")
    Call KillThis("B")
    Application.ScreenUpdating = True
End Sub
 
 
Sub KillThis(str As String)
    Dim fCell As Range
    Dim boolStatus As Boolean
     
    boolStatus = Application.ScreenUpdating
    Application.ScreenUpdating = False
     
     
     
    With ActiveSheet.Cells
        Set fCell = .Find(what:=str, lookat:=xlWhole, MatchCase:=False)
        Do Until fCell Is Nothing
            fCell.EntireRow.Delete
            Set fCell = .Find(what:=str, lookat:=xlWhole, MatchCase:=False)
        Loop
    End With
     
    Application.ScreenUpdating = boolStatus
     
End Sub

Re: vlookup in vba

What do you mean by "set"? Is the ComboBox linked to this cell, and so we could grab the value just from the cell (rather than the ComboBox object)?

Re: vlookup in vba

Ok, I'm going to guess that that table is on a worksheet called "Sheet1", and that col R is what's being fed to the ComboBox, and that you want to VLOOKUP info from col S. I'm not sure what C1 cell is, as you've said you're using an ActiveX dropdown. Is that still true, or is it a Data Validation dropdown?

Sub ExampleCode() 
    Dim myVal As String 
    Dim myResult As String 
     
     myVal = Me.ComboBox1.Value
     
     
     'Try to do a vlookup
    On Error Resume Next 
    myResult = WorksheetFunction.VLookup(myVal, Worksheets("Sheet1").Range("R:U"), 2, 0) 
    On Error Goto 0 
     
     'See if we got a value, or an error
    If myResult = "" Then 
        MsgBox "Value not found" 
    Else 
        MsgBox myResult 
    End If 
End Sub

Re: Deleting rows by value in a given column or selection

Hi TOOMC, and welcome to the forum!

Try running the MainCode macro from here. Will run through and delete rows contain whole matches.

Sub MainCode()
    Application.ScreenUpdating = False
    Call KillThis("A")
    Call KillThis("B")
    Application.ScreenUpdating = True
End Sub




Sub KillThis(str As String)
    Dim fCell As Range
    Dim boolStatus As Boolean
    
    boolStatus = Application.ScreenUpdating
    Application.ScreenUpdating = False
    




    With ActiveSheet.Cells
        Set fCell = .Find(what:=str, lookat:=xlWhole, MatchCase:=False)
        Do Until fCell Is Nothing
            fCell.EntireRow.Delete
        Loop
    End With
    
    Application.ScreenUpdating = boolStatus
    
End Sub

Re: vlookup in vba

Can you elaborate, what didn't work? Like I said, I was having to guess. I've not idea what ranges or values that you're dealing with. If it's not working, please post the code that you used, and explain what happened (code errors out, bad results, unexpected results, etc.)

Re: Excel "=Month" Settings

XL let's you assign custom names to any cell(s) you want. Someone has given the name "month" to the cell B1 on the first sheet (IMO, don't use names that are the same as function, but side issue).

Anyway, names are helpful in that instead of using a cell reference, you can just use the name. So, if you had a range with name "myChoice", then you could use it in a formula like

=VLOOKUP(myChoice, B:C, 2, 0)

instead of having to type

=VLOOKUP(Sheet1!$B$1, B:C, 2, 0)

That said, with that named range in place, whenever you put the name "month", you're creating a cell link to B1 of the first sheet. When the cell is empty, the cell value is 0. You see the same behavior when you have a blank cell of A1 and have another cell with formula
=A1

the result is 0.

Further reading: http://www.ozgrid.com/Excel/named-ranges.htm

Re: Pause a Macro to allow user to Name a Range

Hi morganprop, and welcome to the forum!

Can you just prompt the user with an InputBox?

Sub ExampleCode()
    Dim newName As String
    
    'Some code here
    
    newName = InputBox("What is the name of the range?")
    
    ActiveCell.Name = newName
    
    'Do other stuff here
    
End Sub

Re: vlookup in vba

I'm going to make a lot of guesses and assume this is using a activeX combo box on a sheet, not a user form. Some code like this, perhaps?

Sub ExampleCode()
    Dim myVal As String
    Dim myResult As String
    
     myVal = Me.ComboBox1.Value
    
    'Try to do a vlookup
    On Error Resume Next
    myResult = WorksheetFunction.VLookup(myVal, Range("A:B"), 2, 0)
    On Error GoTo 0
    
    'See if we got a value, or an error
    If myResult = "" Then
        MsgBox "Value not found"
    Else
        MsgBox myResult
    End If
End Sub

Re: vlookup in vba

Hi Acorn11, and welcome to the forum!

I'm a little confused; the code you posted doesn't have a VLookup anywhere in it. Is it this block of code that you're having trouble with, or making a VLookup?

Re: Loop Through Rows in Column and Add Subtotals at Each Empty Row

Hi dashender7, and welcome to the forum!

I've moved the thread for you. See if this code will work for you. It prompts for the column letter you want to place subtotals in. I'm assuming the data you are adding is constants, not formulas.

Sub MakeSubtotals()
    Dim myCol As String
    Dim rngSearch As Range
    Dim rngBlock As Range
    
    myCol = InputBox("What column to place subtotals in?")
    
    If myCol = "" Or myCol = "False" Then Exit Sub
    




    Set rngSearch = ActiveSheet.Range(myCol & ":" & myCol)
    Application.ScreenUpdating = False
    For Each rngBlock In rngSearch.SpecialCells(xlCellTypeConstants).Areas
        rngBlock.Cells(1).Offset(rngBlock.Rows.Count).Formula = "=SUM(" & rngBlock.Address & ")"
    Next
    Application.ScreenUpdating = True
End Sub

Re: Number of Calendar Days between the first Start Date & the last End Date

If not, an alternative structure:

=INT(DAYS360(FirstDateFormula, LastDateFormula)/30)

Re: Number of Calendar Days between the first Start Date & the last End Date

Quote from Carim;795342

Hello Luke :wink:

What about a simple Named Range ... defined dynamically ... ?

Pure genius!

Re: Number of Calendar Days between the first Start Date & the last End Date

Can you use an XL table, so that you can use a structural reference (Insert - Table)?

Re: Number of Calendar Days between the first Start Date & the last End Date

Could do this array:

=MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))

Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.