Posts by StephenR

Apology accepted.

Assuming it hasn't been answered at MrExcel I'll take a look tomorrow.

OK, think I see that but one more question. When you say you want to find the values in B which are >= the value in F, presumably this is only local values in column B, i.e. until the next maxima? So in your v2 file you would start looking at B2:B56? Do I have that right?

Some clarification required I think. You've posted two files but they have different data and column headings. Please could you post an illustrative file with before and after sheets so I'm clear what exactly you need to be done as you seem to have done some of it already? Also, I don't think I understood what you meant by trigger signals.

Could you post a workbook with a sample of your data and expected results? Are you just wanting column E to be completed?

Would this work for you? Place in the sheet module. When you double click it inserts a new row below and copies the formulae from the row above.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With Target
        .Offset(1).EntireRow.Insert
        .EntireRow.Copy .Offset(1).EntireRow
        On Error Resume Next
        .Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    End With
End Sub

Kudos for the word "neophyte" - don't think you have to apologise for your English.

Try this.

Sub x()


Dim r As Long


For r = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(r, 1) > 0 Then
        Range("A1").Offset(, r).Resize(Cells(r, 1)).Insert shift:=xlDown
    End If
Next r


End Sub

[cp]*[/cp]

Try this. The On Error hides errors so you can't see what's going wrong. In your second bit of code you switched from Shapes to Textboxes. Stick to Shapes and you can then check the type of shape before checking the text.

Sub FindInTB()
'On Error Resume Next


    Dim wks As Worksheet, tb As Shape
    Dim sFind As String
    Dim sTemp As String
    Dim iPos As Integer
    Dim Response


    sFind = InputBox("Search for?")
    If Trim(sFind) = "" Then
        MsgBox "Nothing entered"
        Exit Sub
    End If
    sFind = LCase(sFind)
    For Each wks In ActiveWorkbook.Worksheets
        For Each tb In wks.Shapes
            If tb.Type = msoTextBox Then
                sTemp = LCase(tb.TextFrame.Characters.Text)
                iPos = InStr(sTemp, sFind)
                If iPos > 0 Then
                    With tb.TextFrame.Characters(Start:=iPos, Length:=Len(sFind)).Font
                        .ColorIndex = 3
                        .Bold = True
                    End With
                End If
            End If
        Next tb
    Next wks
    MsgBox "Finished"
End Sub

Yes, that's a pain. It usually happens when you right click and View Code while the mouse arrow is inside a control. You can just delete them. Glad it all worked.

The values will not be retained between runs but shouldn't be erased while running the code. Can you upload a small workbook which illustrates the behaviour?

Wrap the code using the # button on the editor or write [code] and closing tag [ /code] (without the space).

Welcome to Ozgrid. Please remember to use code tags in future as per forum rules.

Actually there's nothing obviously wrong with that code, particularly if the first one works. Possibly a silly question but is there definitely text in all the boxes? Could you have any sheet formatting that might affect things - are the values definitely not there as opposed to just being not visible? Also check the textbox names are definitely correct.

Quote from acat

No, that's not it. You didn't take into account the Address part

Don't mention it. Why are you asking a question if you know the answer?

You can do this, and then replace the hard-coded variables with the variable names.

Dim r1 as range, r2 as range


Set r1=Range("E15:J15")
Set r2=Cells(16,c)
'etc

Can you clarify what you want to define as variable in that bit of code?

You could try turning off calculations too (as per your second bit of code).

If your data are in a continuous block you could use AutoFilter. That would avoid a loop altogether.

Limit your range to that containing data (in case it doesn't go down to row 500).

Do you have other code because that code was pretty much instantaneous for me?

If you want to copy hard-coded ranges just use

Sub Button1_Click()
With ActiveSheet
    .Range("A3:A14").Copy Destination:=Workbooks("parentsheet.xlsm").Worksheets("Account").Range("A3:A14")
    .Range("B3:B15").Copy Destination:=Workbooks("parentsheet.xlsm").Worksheets("Account").Range("B3:B15")
End With
End Sub

Try turning off screen updating.

Sub ToggleHideRows()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("P76:P500")
        If Not IsEmpty(c) and c.Value = 0 Then
            c.EntireRow.Hidden = Not c.EntireRow.Hidden
        End If
    Next
    Application.ScreenUpdating = True
End Sub

You could use VLOOKUP (C2 and copy down)

=IFERROR(VLOOKUP(A2,Sheet1!$A$1:$B$20,2,0),"")

or INDEX/MATCH

=IFERROR(INDEX(Sheet1!$B$1:$B$20,MATCH(Report!A2,Sheet1!$A$1:$A$20,0)),"")

In both cases, the IFERROR will show a blank rather than NA error if the name is not found.

I can't explain it but the issue seems to be creating the user form on the fly. If you create a userform and then add your code to the initialize event it will work. There might be a workaround for your scenario but you'll have to wait for someone more knowledgeable than me.