Posts by turtle44

Re: Makro Exel Lotnumber

If you need a macro, let me know, but I think a formula would be easier. Try this:

=DATE(2000+MID(A5,2,2),MID(A5,6,2),1)

Then you can format this cell as date.

Re: Get Cell Value Before Worksheet_change Event Triggers

Mike,

Don't you need to turn off EnableEvents before the Undo?

Re: Get Cell Value Before Worksheet_change Event Triggers

You could get the values when you selected the two cells with a Worksheet_SelectionChange event. Would that help?

Re: Merge 2 Cells With Exception

Try:

=(A2 & " " & left(A3,len(A3)-6))

Re: Formula References Not Updating On Row Insert/Delete

I didn't really look at the code all that closely, but here is a theory I have. Let me know if I am way off.

Your original formula was =Sum(E3:E11)
Let's say, for example, that you then delete 5 rows, so your new formula is =Sum(E3:E6)
Then you add rows. If you add rows between row 3 and row 6, your formula will adjust correctly. If you add rows between row 7 and 11, your formulas would not include the new rows.

Could that be the cause of your problem.

Re: Specific Cell Total

Do a search on "Dynamic Named Ranges" and you'll find your answer.

Re: Deleting Rows With A Macro

Please use code tags when including code in your post.

Try this:

lLastRow = Range("A65536").End(xlUp).Row
For i = lLastRow To 2 Step -1
   If Cells(i, "A") Like "*aol.com" Then
      Rows(i).EntireRow.Delete
   End If
Next i

Re: Add Same Cell From Different Worksheets

I'm not 100% sure what you're asking, but I'll give it a shot. If your sheets are named 1-100, then I think =SUM('1:100'!J11) is what you need. Let me know if I misunderstood the question.

Re: Check Cell Value Length And Uniqueness Before Submission

There must be either a leading or trailing space. Try this.

'  If Len(Cell) <> 9 Then 'remove this line of code
'Replace with
If Len(Trim(Cell))<>9 Then

Re: Screen Updating and Macro Error

Application.ScreenUpdating = False will not prevent these messages from appearing; if you want to suppress these messages, use Application.DisplayAlerts = False. Be sure to use Application.DisplayAlerts = True at the end of your code though.

Re: Extract Alphanumeric String After Hitting First Alpha Character

This is at least your third post this week regarding getting info from alpha-numeric strings. Have you tried modifying the code you received in response to your other posts? I'm not trying to be a jerk, it just doesn't appear that you are putting much effort into solving the problem yourself.

Re: Output Data From A Textbox And Combobox To The Same Cell

Would this work?

ActiveCell.Offset(1, 1) = TxtBCity.Value & ", " & CboState.Value

Re: Check Cell Value Length And Uniqueness Before Submission

Here is a basic outline of the code you will need. You could also use data validation to test for either of these conditions (but not both, at least not to my knowledge) upon data entry into cell.

Sub TestBeforeSubmit()
   Dim rngID As Range 'range with running list of IDs
   Dim Cell As Range 'cell where data is entered


   Set Cell = ThisWorkbook.Sheets("Sheet1").Range("A1")
   Set rngID = ThisWorkbook.Sheets("Sheet2").Range("A1:A50")


   If Len(Cell) <> 9 Then
      MsgBox "Must be 9 characters"
      Exit Sub
   End If


   If Application.WorksheetFunction.CountIf(rngID, Cell.Text) = 0 Then
      'add to list
   Else
      'do not add to list
   End If
End Sub

Re: Desire Versatile Alphanumeric Extractor From Any String

Is this what you need?

Public Function ExtractNumeric(TextString As String) As String
   Dim x As Long
   Dim sDigit As String


   ExtractNumeric = vbNullString
   For x = 1 To Len(TextString)
      sDigit = Mid(TextString, x, 1)
      If sDigit >= "0" And sDigit <= "9" Then
         ExtractNumeric = ExtractNumeric & sDigit
      End If
   Next x
End Function


Public Function ExtractAlpha(TextString As String) As String
   Dim x As Long
   Dim sChar As String


   ExtractAlpha = vbNullString
   For x = 1 To Len(TextString)
      sChar = Mid(TextString, x, 1)
      If sChar Like "[a-zA-Z]" Then
         ExtractAlpha = ExtractAlpha & sChar
      End If
   Next x
End Function

Re: Extract Characters Before And After Middle Symbol

FIND will work in a worksheet, but you'll need to use the INSTR function in a maco

Re: Custom Formula

After each column with "qualifying numbers", insert a column and use the RANK function to get the "place" within that particular column. Sum the values of the rank in each of these new columns to get the total, then RANK the total to get the winner. I've attached a 2 column example.

Re: Finding Text on Varying Workbook "b", Returning 10 Cells Under The Given Text To "a"

This bit of code will copy a 10X10 range of cells starting one cell below the first cell containing the word "Within" on the active worksheet. Is that what you need?

Dim Cell As Range
   Dim rng As Range


   Set Cell = Cells.Find(What:="Within", After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
   Set rng = Range(Cell.Offset(1, 0), Cell.Offset(1, 9))
   rng.Copy

Re: Cell Displays Formula Not Result

Problem solved. Thanks for the replies.

I was inserting the formula in a newly added column (the cells in the new column were formatted as text). After I added the formula, I formatted the cells as General, but it was apparently too late. When I changed the code to format the new column as General before inserting the formula, everything worked fine. Thanks again.

I have a macro that is attempting to insert a UDF into the used range of column B, then replace the formula with values. Here is a portion of the code:

With rng
   .FormulaR1C1 = "=ConvertName97(RC[-1])"
   .Copy
   .PasteSpecial (xlPasteValues)
   Application.CutCopyMode = False
End With

The problem is that the cells in rng display "=ConvertName97(RC[-1])" (without the quotes) instead of the calculated value. If I type the UDF directly into the cell, or if I use the Insert Function Wizard, I get the same results (the Formula Result bar in the Function Wizard gives me the correct value, but the cell still displays the formula).

The only way I can get it to work is the put the formula into another cell in my workbook (either by entering it manually or using the wizard) and then copying it to my range.

I have verified (several times) that Calculation is automatic, and I have also recalculated the sheet. I have also verified that View Formulas is not checked. I am probably overlooking something incredibly simple, I just have no idea what it could be. Any help would be greatly appreciated.