Is it possible to find a certian character in a column if cells?

[DeezNuts]

Curious how would one go about editing something this

=SUMIF($D$6:D8000,">=0", $E$6:E8000)

So if any of the cells which are normally 10.00 had a value of 10% the formula would also search for the % character and ignore that cell if one is found. Is this possible?

I have tried =ISNUMBER(SEARCH but only works for me if the % is by itself if its 25% wont work

 

[bschwartz]

The percent symbol when it's with a number is generally a formatting thing (i.e. the % symbol is not actually there). If you are trying to find numbers that are percentages you can look for numbers smaller than one.

 

[DeezNuts]

After trying a few things I was kinda leaning that way that it cant be done. Thank you for the assistance. losing my mind trying to find a work around to a minor (major) issue

[TABLE="width: 648"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=SUM(IF(1-ISNUMBER(SUBSTITUTE(XEM1048551,"%","")+0),1))[/TD]
[/TR]
[TR]
[TD]=IF(COUNT(SEARCH("~"&RIGHT(XEM1048552,1),"%")),"Y", "N")
[/TD]
[/TR]
[TR]
[TD]=--(LEN(XEM1048552)>LEN(SUBSTITUTE(XEM1048552,"%","")))[/TD]
[/TR]
[TR]
[TD]=--NOT(ISERROR(FIND("%",XEM1048552)))[/TD]
[/TR]
[TR]
[TD]=IF(SEARCH("%",XEM1048552&"%"),"",SUMIF($D$6:D8000,">=0",$E$6:E8000))[/TD]
[/TR]
[TR]
[TD]=IF(ISNUMBER(SEARCH("%",XEM1048552)),"Y", "N")
[/TD]
[/TR]
[TR]
[TD]=IF(SUBSTITUTE(XEM1048552,"%","")<>XEM1048552,"Y","N")[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

You could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function SumNoPercent(r As Range) As Single
  Dim c As Range
  
  For Each c In r
    If Right(c.Text, 1) <> "%" Then SumNoPercent = SumNoPercent + c.Value
  Next c
End Function

Excel Workbook
	B
1
2	10
3	10%
4	0.056
5	0.03%
6	20
7	40
8
9	70.056
SUM Excluding %

 

[DeezNuts]

That Sir is very cleaver thank you. That works great.

Have a question is it possible to format certain columns (more so every third cell in certain columns) using vba? So for instance in my form I put any number without a % it formats as general but if I input a number with a % it format the cell as percent? Currently when the form tries to put say 90% on the sheet it makes 90.00. Just curious trying to think of a way to repair a design flaw is all.

 

[Peter_SSs]

That Sir is very cleaver thank you. That works great.

Glad it helped.

.. is it possible to format certain columns (more so every third cell in certain columns) using vba?

Yes. However, I didn't fully understand the rest of your explanation. To provide suggested code I would nee to know
- which column?
- starting at which row?
- where do we finish doing this? That is, how many rows should be done? Or what is the finishing row?
- what is the formatting of the majority of cells to be? That is, the first two of each group.
- what is the formatting of the third cell in each group to be?
- when is the formatting to be applied? Before/after data is entered.

 

[DeezNuts]

Honestly I am terrible at trying to explain things but I will give it a go using the actual code.

Private Sub cmdSubmit_Click()
Application.ScreenUpdating = False
    Dim NextRw As Long
    Dim ws As Worksheet
    Dim startRow As Long
  
    
    Set ws = Courses
    NextRw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
        If NextRw < 6 Then NextRw = 6
     '<< Add data to worksheet >>
    ws.Cells(NextRw + 0, "A") = Me.txtAssignmentName.Value
    ws.Cells(NextRw + 1, "A") = Me.txtDate.Value
    ws.Cells(NextRw + 2, "A") = Me.txtAssignmentType.Value
        If (Me.txtPointsReceived.Value & "X" = "X") Then
        ws.Cells(NextRw + 0, "D").Value = "-"
        Else
        ws.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value '< If value contains % format cell for percent else if no % format as number 
                                                                     '< (ex. 20.00) prior to inputting the value
        End If
    ws.Cells(NextRw + 0, "E") = Me.txtPointsPossible.Value '< If value contains % format cell for percent else if no % format as custom (ex. "/" 0.00)
                                                           '< prior to inputting the value
 
 Unload Me
End Sub

Only two possible value input styles one style would be say 90% out of 100% and the other would be 10 out of 10. Is this achievable with vba?

 

[Peter_SSs]

Not sure I have all the specific detail and I don't have the UserForm or data to test, but see if this is headed in the right direction - from which you might be able to tweak it to your specific needs.

        If (Me.txtPointsReceived.Value & "X" = "X") Then
        ws.Cells(NextRw + 0, "D").Value = "-"
        Else
        ws.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value '< If value contains % format cell for percent else if no % format as number 
                                                                     '< (ex. 20.00) prior to inputting the value
        End If
    ws.Cells(NextRw + 0, "E") = Me.txtPointsPossible.Value '< If value contains % format cell for percent else if no % format as custom (ex. "/" 0.00)
                                                           '< prior to inputting the value

Try replacing the above code with that below.
You'll also an extra Dim statement to Dim CelFormat as String

If (Me.txtPointsReceived.Value & "X" = "X") Then
  ws.Cells(NextRw + 0, "D").Value = "-"
Else
  If InStr(1, txtPointsReceived, "%") = 0 Then
    CelFormat = "0.00"
  Else
    CelFormat = "0.00%"
  End If
  With ws.Cells(NextRw + 0, "D")
    .NumberFormat = CelFormat
    .Value = Me.txtPointsReceived.Value
  End With
End If
If InStr(1, txtPointsPossible, "%") = 0 Then
  CelFormat = """/"" 0.00"
Else
  CelFormat = "0.00%"
End If
With ws.Cells(NextRw + 0, "E")
  .NumberFormat = CelFormat
  .Value = Me.txtPointsPossible.Value
End With

Note too that those red "+ 0" bits are not required, but don't do any harm.

 

[DeezNuts]

Peter, thank you so much, that works beautifully. I appreciate all your work on this.

 

[Peter_SSs]

Peter, thank you so much, that works beautifully. I appreciate all your work on this.

No problem, glad it worked for you.