Posts by norie

So if the user did enter 'Balloon type 2' what would you expect to be returned?

By the way, where does the userform come into things?

Something like 42991.438599537 is a 'true' date value, what I was actually wondering was if you wanted it in that format or formatted as a date?

Do you actually want the numerical value or are you trying to convert these values to 'true' date values?

How is the dropdown populated?

Is it linked to a cell?

Re: VLookup Not Working for Change Event Sub

Is the line of code actually being executed?

Re: 3 Team 8 Hour Rotating Shift Schedule Generator ($25 USD)

You want a 3 Team 8 Hour Rotating Shift Schedule Generator for $25?

Re: Need help with Vlookup not working

Could the problem be there are no matches because every value in column B starts with SJ?

Re: Assigning a range of variables to another variable (Worksheet names)

You can't work with variables like that in VBA.

Have you considered using arrays?

Re: Using Range with Dim and For yields Application-defined or object-defined error

Cells also needs a worksheet reference, try this.

With  Sheets("one")
    .Range(.Cells(31 * i - 26, 2), .Cells(31 * i - 3, 4)).Copy Sheets("two").Range("A2") 
End With

Also, it should just be Cells not Range(Cells)

Sheets("three").Range("B4").Copy Sheets("four").Cells(1 * i + 6, 3) 
        Sheets("three").Range("B5").Copy Sheets("four").Cells(1 * i + 6, 4)
        Sheets("three").Range("B6").Copy Sheets("four").Cells(1 * i + 6, 5)

Re: looping column worksheetfunction vlookup

Instead of using multiple Vlookups function where the column changes you could use one Match function to find the row where cl is in the table.

Then you could use Cells to return the values you want, or you might even be able to use resize and transpose.

Re: go to cell of match result

Dave

It should just be match_range not Range(match_range).

There is another problem though, what exactly are you trying to do here?

next_cell = Application.goto match_range.Cells(Application.Match(x.Value, match_range, 0))

You appear to want to goto a cell in match_range and assign a value from the cell you goto to the variable next_cell.

That's not possible with the above code.

Also later on in the code you have this.

Cells(next_cell).Select 
ActiveCell.Value = temp_result 
next_cell = next_cell.Offset(0, 1)

What are you trying to do here?

Re: Applying a border to the same cells on every worksheet with VBA

Eh, I know it doesn't work.:)

Do you mean that wherever 'P12' is found in a cell you want to format that cell and the five cells below it?

If you do then this might work.

With rng.Resize(6).Borders(xlEdgeLeft) 
    .LineStyle = xlContinuous 
    .ColorIndex = 0 
    .TintAndShade = 0 
    .Weight = xlMedium 
End With

By the way it still isn't clear where you want to loop to.

Re: Applying a border to the same cells on every worksheet with VBA

Based solely on the posted code, which isn't really clear, try this.

Sub Border_For_P12_Loop_All_Sheets()
Dim shts, ws As Excel.Worksheet
Dim rng As Range


    shts = Array(Sheet1, Sheet2, Sheet3)


    For Each ws In Sheets


        With ws


            Set rng = .Range("C1")


            Do
                If rng.Value <> "p12" Then
                    Set rng = rng.Offset(1, 0)
                Else
                    With .Range("C2:C7").Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                    Set rng = rng.Offset(1)


                End If


            Loop Until rng = Range("C28")
            
        End With
    Next


End Sub

The 2 main things that aren't clear are why a static range like 'C2:C7' to apply the formatting and this,

Loop Until ActiveCell = .Range("C28")

are you trying to loop until the active cell value equals the value in C28?

Re: ObjectButtons selections that looks up value in table array

Mike

Can't you use the option button captions?

Re: How to run vba faster with this code

What, in words, is the code meant to do?

By the way, adding a progress bar could possibly slow things down further.

Re: Copy Excel range into email - Type mismatch error

If you are going to use On Error like that, ie to call code that's at the end, you need to exit the sub before it reaches that code or it will also be executed when there are no errors.

Re: Application-defined or object_defined error

What range are you trying to refer to with Range("AF")?

'AF' on its own is not a valid range reference.

Re: ADODB connection error when process is called from 64-bit Excel

If you are going to cross-post please post links to the thread(s) on the other forum(s).

Re: downloaded file from SAP can't open using VBA

Why use Application.Path though?

That's the path to the Excel application, I doubt very much that files would be downloaded to there.

Re: downloaded file from SAP can't open using VBA

Sorry I don't understand.

When you use Application.Path the code doesn't work but when you use "C:\Next\forward\folder\name" it does work, so why are you using Application.Path?