Posts by jproffer

Re: VBA CODE for Excel 2007 & 2010

Do you want this input box to be a seperate action from the copying you're doing now?

As in, have to buttons...one to copy as before and one to get a user input?

Sounds like, so I'll work on that. If not, please advise.

Re: VBA CODE for Excel 2007 & 2010

forum.ozgrid.com/index.php?attachment/44876/

I didn't see you attachment until I was done with this, but the code should move over pretty easily. I named the sheets, "Sheet1" and "Sheet2" as in your post.

Re: Sheet Name in a cell

I needed permission to access that document, but see this example.

forum.ozgrid.com/index.php?attachment/44844/

The formulas will update as you change sheet names, as it says.

If that doesn't help, I'll be glad to look at your spreadsheet if you attach it here directly, or unlock it on GD.

Re: Clear Cell in column F if cell in D is cleared

This seems to do what you want, under mild testing. Test it out and see...post back if you have trouble.

If Target.Column = 4 Then
    If UCase(Target) = "PASS" Then
        Me.Unprotect
        Range("F" & Target.Row).Value = Date & " " & Time & " by " & Range("B1")
        Me.Protect
    End If
    If UCase(Target) = "CHANGED" Then
        Me.Unprotect
        Range("F" & Target.Row).Value = Date & " " & Time & " by " & Range("B1")
        Me.Protect
    End If
    If UCase(Target) = "FAIL" Then
        Me.Unprotect
        Range("F" & Target.Row).Value = Date & " " & Time & " by " & Range("B1")
        Me.Protect
    End If
 
    If Target = "" Then
        Me.Unprotect
        Range("F" & Target.Row).Value = ""
        Me.Protect
    End If
 
End If

Re: Sheet Name in a cell

Summary Sheet, cell A1:
=MID(CELL("filename",'Sheet A'!A1),FIND("]",CELL("filename",'Sheet A'!A1))+1,31)

Summary Sheet, cell A2:
=MID(CELL("filename",'Sheet B'!A1),FIND("]",CELL("filename",'Sheet B'!A1))+1,31)

as long as the file has a path (I.E. - has been saved to file), this will work.

Re: Resizing working area (stop scrolling through empty rows)

I was taking a look at this and noticed that code above is only good for columns up to "Z" (single letter columns). Hopefully you haven't found this out the hard way yet, but here is a better one that takes multi-letter columns into account:

Sub Reset_Used_Range()
Dim LC1 As Long
Dim LC2 As String
Dim LC2Start As Integer
Dim LC2End As Integer
Dim LastCol As String
Dim LastRw As Long
Dim FarCol As String
If Application.Version >= 12# Then
    FarCol = "XFD"
Else
    FarCol = "IV"
End If


LC1 = ActiveSheet.UsedRange.Columns.Count + 1
LC2 = Cells(1, LC1).Address


LC2Start = InStr(1, LC2, "$") + 1
LC2End = InStr(LC2Start, LC2, "$")


LastCol = Mid(LC2, InStr(1, LC2, "$") + 1, LC2End - LC2Start)
LastRw = ActiveSheet.UsedRange.Rows.Count + 1
ActiveSheet.Rows(LastRw & ":" & Rows.Count).EntireRow.Delete
ActiveSheet.Columns(LastCol & ":" & FarCol).EntireColumn.Delete


End Sub

Re: Resizing working area (stop scrolling through empty rows)

Simplest way is to run this:

Sub Reset_Used_Range()
Dim LC1 As Long
Dim LC2 As String
Dim LastCol As String
Dim LastRw As Long
Dim FarCol As String
If Application.Version >= 12# Then
    FarCol = "XFD"
Else
    FarCol = "IV"
End If


LC1 = ActiveSheet.UsedRange.Columns.Count + 1
LC2 = Cells(1, LC1).Address
LastCol = Mid(LC2, InStr(1, LC2, ":") + 2, 1)
LastRw = ActiveSheet.UsedRange.Rows.Count + 1
ActiveSheet.Rows(LastRw & ":" & Rows.Count).EntireRow.Delete
ActiveSheet.Columns(LastCol & ":" & FarCol).EntireColumn.Delete


End Sub

Please test on a copy first to make sure nothing you need gets deleted.

Re: Conditional formatting, deleting first character in a cell but keeping the format

No bother. Glad you got it figured out

Re: Add Spaces in a string of numbers

This can be done with a formula. Try:

=IF(LEN(A1)=6,LEFT(A1,2) & " " & MID(A1,3,2) & " " & TEXT(RIGHT(A1,2),"00.00"),LEFT(A1,3) & " " & MID(A1,4,2) & " " & TEXT(RIGHT(A1,2),"00.00"))

assuming the first one is in A1.

Re: Workbook Expiration

Workbook_Open()
Dim ExpDate as Date


ExpDate = "May 19, 2012"


    If Now >= ExpDate Then
        Application.DisplayAlerts = False
        ThisWorkbook.Close
        Application.DisplayAlerts = True
    End If
End Sub

This is how I always do it. Of course you have to lock the VBA project, and you have to change the ExpDate for each release of the workbook.

This is from memory, but it looks right

Re: Defined Function does not work

That was my point, but whatever he wants

Native functions...even ones that are deeply nested...are normally more efficient than a UDF.

Re: Defined Function does not work

Ok. I'm glad you figured it out even if I don't see the point of it

Re: Defined Function does not work

Ok then. I don't know what else to do without more details. Sorry.

If that's not what you want...instead of "don't work", maybe you could explain what you DO want to happen.

Re: Defined Function does not work

It's not a function that will return anything. It's a subroutine. Click the button on the worksheet.

That's all it does because, as I said, I'm not sure what you want to do with it.

forum.ozgrid.com/index.php?attachment/44555/

Re: Defined Function does not work

Sub MyValue()
Dim MyVar As Variant
    MyVar = Range("B9").Value
        MsgBox MyVar
        
End Sub

I don't think this is what you want, but it's a shot in the dark because "I want the result of B9 in VBA" could mean anything.

Re: Defined Function does not work

Figured that

The function isn't in your workbook. So you couldn't use it there, even if it was well formed, which it's not.

Other than "trying to use the function", what are you trying to do...with your data??

You wanted to find the max based on date, and you did that in cell B9. If you're trying to insert that formula in a cell, you will have to use a sub not a function.

Re: Defined Function does not work

The UDF name isn't defined at the end, and it looks like cell B9 is doing what you want in your worksheet.

I don't understand what you're trying to do.

Re: absolute average formula

I don't know what your ranges are, but it would be something more like:

=ABS(AVERAGE(Range))

Re: Conditional formatting, deleting first character in a cell but keeping the format

In conditional formatting, choose "Use a formula to....." and enter:

=LEFT($B1,1)="r"

Set your format.

add the other 2 formats.

$B1 refers to whatever cell the value is actually in. It may seem like a circular reference, but in this case it's not.

You should be able to drag it down and then select Fill Formatting Only

Re: Recalculate only the Current Workbook

You won't make many friends cross-posting. See the answer given on your MrExcel thread