Posts by yegarboy

Re: Highlight only if conditional duplicates exsist

Thats the ticket! It works like a charm. Thanks for your help Rob.

Re: Highlight only if conditional duplicates exsist

Thanks Rob, but there is one tiny problem. How do i make it ignore blank cells? Here is the exact way i have it entered.


=(SUMPRODUCT(($F$7:$F$1500=$F7)*($H$7:$H$1500=$H7))>1

Thanks for looking guys

I have a sheet with 10 columns and 1500 rows. what i am looking to do is a conditional format that only flags if 2 sets of duplicates exsist.

For example A1 has 1 And B1 has 11
Then A2 has 1 and B2 has 22. I dont want A1 And A2 to flag as a duplicate because B1 and B2 are different. Basiclly only flag if those two cells in a row match numbers throughout the sheet.

Thanks in advance for your help

Re: Can Inputbox display a partly dynamic message based on Columns?

Take this for a spin.

Dim ce As Range


  On Error Resume Next
  Lastrow = Worksheets("Datasheet").Cells(Rows.Count, 1).End(xlUp).Row
  
  For Each ce In Range("BI2:BI" & Lastrow)
    
    getchoice = ""
    
    With getchoice <> "0" And getchoice <> "1" And getchoice <> "2" ' 0 is 1st, 1 is 2nd, 2 is 3rd
      getchoice = InputBox("Enter 0,1 or 2")
        Prompt = "This Class & RC[3] & 'Name' & RC[14]" ' Supposed to refer to column C a N relatively. Not working
        ' Need a skip or exit option if user doesn't want to enter in vlaues.
        End With
    If getchoice = "" Then
    GoTo exitsub
    End If
    
   
 
 
    Select Case getchoice
      Case "0"
        ce.FormulaR1C1 = _
        "=IF(AND(RC[-19]<>0,((RC[-18]*1.65)+(RC[-17])+(RC[-16]*0.8))<>0, _(RC[-20]-(RC[-20]/((RC[-18]*1.65)+(RC[-17])+(RC[-16]*0.8)))*((RC[-17])+(RC[-16]*0.8)))/RC[-19],0)*1.25"
        
      Case "1"
        ce.FormulaR1C1 = _
        "=(IF(RC[-14]<>0,(RC[-15]-(RC[-15]/((RC[-13]*1.65)+(RC[-12])+(RC[-11]*0.8)))*((RC[-12])+(RC[-11]*0.8)))/RC[-14],0)*1.25)"
      Case "2"
        If Range("V2") > 17 Then
            ce.FormulaR1C1 = _
        "=(IF(RC[-39]<>0,(RC[-40]-(RC[-40]/((RC[-38]*1.65)+(RC[-37])+(RC[-36]*0.8)))*((RC[-37])+(RC[-36]*0.8)))/RC[-39],0))"
        ElseIf Range("V2") < 18 Then
            ce.FormulaR1C1 = _
        "=(IF(RC[-39]<>0,(RC[-40]-(RC[-40]/((RC[-38]*1.65)+(RC[-37])+(RC[-36]*0.8)))*((RC[-37])+(RC[-36]*0.8)))/RC[-39],0)*0.725)"
        Else
    End If
    
exitsub:
   Dim iResponce As Integer
iResponce = MsgBox("Are You Sure", vbYesNo, "DELETE")
If iResponce = vbYes Then ' They Clicked YES!
   End
Else
End If
 
End Select
Next ce

Re: Can Inputbox display a partly dynamic message based on Columns?

This should do what you want for the exit sub

Dim iResponce As Integer
iResponce = MsgBox("Are You Sure", vbYesNo, "Exit?")
If iResponce = vbYes Then ' You Clicked YES!
    ' PUT YES CODE HERE!
Else
   ' PUT NO CODE HERE!
End If

I am learning listboxes today My code below populates a listbox from a searched range. Everything works fine but i would like to know how to make this code display 10 columns instead of 2. Thanks for your time.

the column count function doesnt work.

Dim rng As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    With Range("A6").CurrentRegion
        .AutoFilter 5, "*" & Me.TextBox1.Text & "*"
        Load Userform8
        Userform8.LsBx_View.Clear
        For Each rng In .Offset(1).Resize(.Rows.Count - 1).Columns(5).SpecialCells(xlCellTypeVisible)
            'description as column 1 in the listbox
            Userform8.LsBx_View.AddItem rng.Value
            'price as column 2
            Userform8.LsBx_View.List(Userform8.LsBx_View.ListCount - 2, 1) = rng.Offset(, 1).Value
            'row number as column 3
            Userform8.LsBx_View.List(Userform8.LsBx_View.ListCount - 1, 2) = rng.Row
        Next
        .AutoFilter
    End With
    Unload UserForm3
    Userform8.Show
    Application.ScreenUpdating = True

Re: USD $30 To populate 2 userforms and resubmit data (3) Codes

Its perfect Wigi THANK YOU!!!!!

Awaiting Payment Info

Re: Change font color based on cell value

Take a look at the link YuraYong provided. I believe that will be your best shot. The code i provided was a recording of setting a conditional format. HTH


Is your data on a worksheet or a userform?

Re: Creating ranks with suffix "T" in case of a tied position

Can you include your rank formula in place as well? Basiclly make it function. thanks

Re: Creating ranks with suffix "T" in case of a tied position

Hey Hrithik,

Upload a sample book with the data filled in where you want it and how you would like the result to displayed.

Re: Array Function Help: Rank within a conditional range

=SUM(IF($K$25:$K$30="Yes",$L$25:$L$L30))

Something like this? this is an array

Re: USD $30 To populate 2 userforms and resubmit data (3) Codes

Thank you very much Wigi. I await your progress. Thank you.

Re: USD $30 To populate 2 userforms and resubmit data (3) Codes

Im working with Wigi

Workbook sent

My workbook will need e-mailed. I have 3 userforms. UF3 searches a range. UF8 i want to display the search. UF7 i want to populate from search if edit button is pressed on UF8 and resubmit the data back to the sheet. Shouldnt be to hard. Its just way out of my scope. Im pretty happy with having multiple Userforms if its doable. Looking forward to getting this solved,Thanks Guys

Re: Populate Userform From A Listbox

Thanks for the pointers Rob, i didnt mean to come off like i wanted someone to do it for me. I apologize for that. I was just seeing how others would have gone about it. Like you just did Thanks again

Re: Populate Userform From A Listbox

That would be ok, but how would i program that? I would still need the listbox to display all the results, then when i pressed edit the boxes would be populated.then edit/resubmit the data to thier oridginal cells. Sorry if im asking alot.

Re: Populate Userform From A Listbox

What?

Re: Writing an "IF" formula change text color

This should get you started. Place it in the worksheet modual.

Range("A1:A15").Select 'Edit what range this condition applys to EG A1:A5 ect
 
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=2" 'Change this value the number you want to be equal to or less than
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961  ' Color is Red
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 
    End

Its a conditional format macro. Im new to VBA also and just thought i would try and help. :smile:

I have searched all day for something close and havent turned up anything my retarded self could use.
I have 2 userforms. Userform 1 submits data to a sheet. Userform 2 displays all data on the sheet in a listbox. So far everything works as it should. What i would like to do is have a 3rd userform that userform 2 "With the listbox" can update to for editing. My 3rd userform will be identical to the first. So i would like to send data from the listbox to the userform/get edited and then resubited to the sheet.Any clue on how to accomplish this? Thanks in advance.......You Guys Rock!!!

:music:

Re: Display StrMessage In Userform Listbox

Ok here is a very chopped down version for you. The code is in userform3 in the command button.
Thanks for your help.


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