Posts by Brian Walters

Re: Randomly Select 25 Records out of 500 and Enter a Y in empty Column

Try adding the one row I added below:

Sub RandomRows()
    Dim d As Object, r As Range, vKeys, x&
     'get a set of 25 unique numbers
    Set d = CreateObject("Scripting.Dictionary")
    While d.Count < 26
         'Define the min,max of your numbers
        Y = "Y"
        x = RndBetween(10, 510)
        If Not d.Exists(x) Then d.Add x, Empty
    Wend
     'Create a multiarea range
    vKeys = d.keys
    Set r = Rows(vKeys(0))
    For x = 1 To UBound(vKeys)
        Set r = Union(r, Rows(vKeys(x)))
        Range("A" & vKeys(x)).Value = "Y"     '*****   ADDED   *****
    Next
     'Select it
     
End Sub
Function RndBetween(low&, high&) As Long
    RndBetween = CLng(Rnd * (high - low)) + low
End Function

Re: Copy rows with values above x from multiple sheets & copy sheet name to column A

Try this code:

Public Sub MergeData()
Dim ws As Worksheet, i As Long


  For Each ws In Worksheets
    If Not (ws.Name = "Amendments" Or ws.Name = "Common Process Risks" Or ws.Name = "Master") Then
      ws.Select
      For i = 11 To Selection.SpecialCells(xlCellTypeLastCell).Row
        If Range("L" & i).Value > 120 Then
          Range(Range("A" & i), Cells(i, Selection.SpecialCells(xlCellTypeLastCell).Column)).Copy
          Sheets("Master").Select
            Range("B" & Selection.SpecialCells(xlCellTypeLastCell).Row + 1).PasteSpecial xlPasteValues
            Calculate
            Range("A" & Selection.SpecialCells(xlCellTypeLastCell).Row).Value = ws.Name
          ws.Select
        End If
      Next i
    End If
  Next ws
Sheets("Master").Select


End Sub

Re: Need Help for 1 click print with data calculation include

CTRL + Pause\Break will stop the macro in debugging mode.

Re: Return Formula, not Value

You could use a VB worksheet macro such as:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Row = 1 And Target.Column = 1 Then
  Range("B3").Value = "'" & Range("BA2").Offset(0, Range("A1").Value).Formula
End If


End Sub

Re: Calculate task completion date over multiple shifts omitting breaks

If you can tolerate using a macro to populate the result, you can use the attached file.

Re: VBA replacing a formula with a macro returns #Value (Wrong data type)

You need to move the section in the macro that creates the "Apr 2013 details" sheet before the section that creates the "Apr 2013" sheet because you create the "Apr 2013" sheet and change the formulas to refer to a sheet that doesn't exist yet, then when you create that sheet it's too late and the formulas won't correct themselves. If you create the "details" sheet first, then it works.

Re: Need Help for 1 click print with data calculation include

Try using a loop. For example:

Private Sub CommandButton1_Click()     
     
    Dim x As Integer 
    Dim y As Integer 
    Dim i As Integer 
    
    For i = 0 to 9 
      Range("D7").Value = Range("D7").Value + i 
      Range("F7").Value = Range("F7").Value + i
      Sheets("Sample").PrintOut Copies:=1, Collate:=True
    Next i 
     
End Sub

Re: Need to create a formula that will add the weekly pa amount to the YTD for stub t

Will you be storing the weekly column 3 values somewhere? If not, then a formula won't help, but you could make a macro that will add them to column 4.

Re: VBA replacing a formula with a macro returns #Value (Wrong data type)

The attached file works for me. Can you compare it to yours to see what may be causing the problem? Otherwise, you may need to upload your file so that I can see what is happening.

Re: Vba Formula To Reference Offset Cell

Try this

Range("energy").FormulaR1C1 = "=((tms-offset(tms,0,1))/offset(tms,0,1))*100"

Re: Vba Formula To Reference Offset Cell

Is tms a range name in your spreadsheet or a variable in the macro?

Re: Copy Data To Next Available Cell In A Row

Try modifying this

Cells(3,Application.WorksheetFunction.Max(20,Range("IV3").End(xlToLeft).Column)+1).PasteSpecial xlPasteAll

HTH

Re: Extract Numbers And Move To End Of Number

Try this =RIGHT(A1,LEN(A1)-2)&LEFT(A1,2)

Re: Build Formula For Calculation Of Overtime

Try these
• 1.5x Hrs = IF(H2<=40,0,IF((H2-40)<=8,MAX(0,(H2-40)-MAX(H2-48,U2)),(H2-40)-MAX(H2-48,U2)))
• 2x Hrs = MAX(H2-48,U2)

Re: Vb Code

Under Tools-Macro-Macros choose the macro to run and then click Run.

Re: Invoices Follow-up: Conditional Formatting

What does the error message say?

Re: Improve Company’s Use Of Workbooks

The only advice I can offer is to look at Dave Hawley's suggestions on properly setting up workbooks in his sticky post http://www.ozgrid.com/forum/showthread.php?t=76234.

HTH

Re: Look Up And Return Value By Multiple Matching And Unmatching Criteria

Welcome to the forum.

The attached file should do what you need using the Database Functions. Enter your criteria in the green cells.

HTH

Re: Invoices Follow-up: Conditional Formatting

Attached is a MS Word document showing a screen shot of what the Conditional Formatting settings should look like. Highlight the entire row and then enter these into the Conditional Formatting prompt.

HTH

Re: Copy Row To One Of Two Worksheets Based On Match Condition

I don't know if you saw that I edited my post to show how to get all 4 columns of data (the second VB code section). It is a little easier if we can predefine the columns chosen, but if you need that to be variable, let me know.