Posts by JohnCleary

Re: BA code to open a site (intranet) and look for the link name (MyLink) and click.

It seems that it is a duplicate of your other thread on this forum: http://www.ozgrid.com/forum/showthread.php?t=199783

Which also means it is a duplicate of a thread on MrExcel.

You should read the board rules (both here and on MrExcel). Click the link under your name at the top left of the post.

Re: Get URL from intranet link name...need help.

Cross posted on MrExcel.

Re: With Outmail - Conditional Email String for .HTMLBody tag

They're all easy when resolved... No probs.

Re: With Outmail - Conditional Email String for .HTMLBody tag

Is possibly the SET statement - this is only used to assign Objects to a variable. A String is not an Object, but a simple string.

Untested, unchecked - I now have a headache from reading that and scrolling left and right like a lunatic (Not your fault - it's the way the board parses code... On second thoughts it IS your fault. Use Line continuations. It'll help you too)

Re: Copy Text based on Criteria

Assumptions as there has been no reply:
The values go to the last used row on Dashboard
The code runs in the code module of the sheet 'InsufficientSoH'

Sub x()
   
   Dim r As Excel.Range
   Dim c As Excel.Range
   
   Dim lngRow As Long
   
   For Each c In Range("F2", Cells(Rows.Count, 6).End(xlUp).Address)
   
      If IsNumeric(c.Value) Then
         
         With Sheets("Dashboard")
         
            lngRow = .Cells(Rows.Count, 1).End(xlUp).Row
            
            .Cells(lngRow, 1).Value = Cells(c.Row, 1).Value
            .Cells(lngRow, 2).Value = Cells(c.Row, 4).Value
            .Cells(lngRow, 3).Value = Cells(c.Row, 6).Value
         End With
      End If
   Next
            
End Sub

Re: Copy Text based on Criteria

Your initial post should have started "I have a spreadsheet ...". Everything before that is pointless.

You want to copy the values from Cols A, D & F to the sheet Dashboard where F contains a numeric value?

Where on the destination sheet do these value go?

Re: Copy Text based on Criteria

Is it a test/evaluation for work? Only curious as the question you posted is found, verbatim, on another site...

Re: Excel 2013 Inserting Dynamic Named Range

Go explore the tabs - Formulas/Name Manager.

Re: Add a Second Event Handler

In general, native worksheet functions are preferable to VBA, if for no other reason than they are a lot faster. I don't believe you need VBA for this but if that's your preference, then that's your preference.

Re: Add a Second Event Handler

Quote

the fact that B15 contains a formula made it impossible for me to get conditional formatting to work

Disagree. There is absolutely no problem having Conditional Formatting in a cell that already contains a 'standard' formula.

Re: Add a Second Event Handler

After reading your post again, I don't understand the relationship between J2 & B15. You say "whenever the value in Range("J2") on that worksheet is changed" you check the value in B15. What I don't understand is If B15 does not refer to J2 by means of a formula why are you checking B15?

I don't think you need code for this, Conditional Formatting in B15 using =B15<>"" as the fomula and the formatting set to highlight the cell with the appropriate colour (I've no idea which colour ColorIndex 35 is).

Re: Add a Second Event Handler

Does J2 contain a formula or is it input manually?

Re: Macro that populates a cell on based on the value of another cell

Maybe

Sub x()


   Dim c As Excel.Range
   
   For Each c In Range(Range("H2"), Range("H" & Rows.Count).End(xlUp))
      
      If c.Value = 5254 Or c.Value = 5255 Then
         c.Offset(, 5).Value = "Yes"
      End If
   Next
   
End Sub

Using default references to the range so must go in the worksheet code module

Re: Excel uniqe values needed

Waiting for the second request to be answered.

Re: Excel uniqe values needed

What is a "unique set"? Explain exactly what makes a row unique and then add links to your posts on other boards

Re: Find PivotTable Name and set as PivotTable variable in VBA

You don't understand the concept of the 'Index' property. Every object, whether a Worksheet, Workbook, Chart or Pivot Table has an index property- and for any particular type of object, the index will start at 1 and continue the sequence for other objects of the same type. Index 1 will be the first object of that type created in that context.

It doesn't matter what the name of the pivot table is - the first pivot will have an Index of 1, so

Set pvt = ActiveSheet.PivotTables(1)

will always set a reference to the first PT, even if it's Name is 'PivotTableWastingSpaceInTheWorksheet 99"

Of course, if there's more than one Pivot Table on a sheet and you don't know the name of the one you want or any other identifying feature like the number of columns, a unique Row/column label, Heading etc, then the technical term for that is "You're Stuffed".

Another property, Count, can be queried to find out how many objects of a particular type exist...

If ActiveSheet.PivotTables.Count <> 1 Then   
    MsgBox "Dunno wat 2 du", vbShreik
End if

Re: Find PivotTable Name and set as PivotTable variable in VBA

Silly examples

Sets a reference to a pivottable using its index, then by using a name - but as you don't know that I guess you'll have to rely on there being only 1 pivot per sheet.

Sub z()
   
   Dim pvt As PivotTable
   Dim pvt2 As PivotTable
   
   Set pvt = ActiveSheet.PivotTables(1)
   Set pvt2 = ActiveSheet.PivotTables(pvt.Name)
   
   Debug.Print pvt.Name
   Debug.Print pvt2.Name
   
   
End Sub

Re: Extract Variable Number of Rows on Multiple Worksheets to one Master Tab

Quote

replace it with the below.

I sorry, but that will error, SourceWS was declarared as a Worksheet, vSheets as a variant array. You cannot assign anything to a worksheet object except another worksheet object

The code was tested and it works.

If there's an issue then it's something to do with the local environment. For example, the code contains the comment

Quote

' add all sheets to copy

Was that done? Do the names added exactly match the actual names of the worksheets?

Another example. The code works with the active workbook. If you run the code from the VBA editor when a different workbook is active in the Excel window, then it will error.

Re: Print sheets from all .XLSX documents in a folder and its sub folders.

The code is fine - most likely there's some error with one of the worksheet, perhaps it doesn't exist? Change the error handler to show a message box if any errors happen

Catch:
    
    If Err.Number <> 0 Then
      MsgBox Err.Description
      debug.assert false
      resume
    End If
    
    Application.ScreenUpdating = True

After the message box, the code window will be displayed and you use F8 to step the code - it will return to the line that caused the error so you can check.

Re: Extract Variable Number of Rows on Multiple Worksheets to one Master Tab

Maybe this might help?

Sub JC()




   Dim rng As Excel.Range
   Dim CopyToRow As Long
   Dim vSheets As Variant
   Dim vSheet As Variant
   
   Dim SourceWS As Excel.Worksheet
   Dim DestWS As Excel.Worksheet
   
   ' add all sheets to copy
   vSheets = Array("US", "GB", "SE")
   Set DestWS = Sheets("Consolidated")
   
   For Each vSheet In vSheets
      
      Set SourceWS = Sheets(vSheet)
      Set rng = SourceWS.Range("A13:D" & SourceWS.Cells(Rows.Count, "D").End(xlUp).Row)
      
      CopyToRow = DestWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
      rng.Copy DestWS.Cells(CopyToRow, 1)
   Next
   
End Sub

The data being copied should be just values - formulas will probably mess it up a bit.