Posts by mrfitness

Re: Determine Values In A Range Are Numeric

Perfect thank you! I really appreciate it http://www.ozgrid.com/forum/images/smilie/smile.gif

Quote from Kenneth Hobson

Code

Sub FindNonNumerics()
  Dim cell As Range
    For Each cell In [A1:A10]
        If Not IsNumeric(cell) And cell.Value <> "BASE" And cell.Value <> "A/W" Then
            MsgBox ("Non-numeric cell, " & cell.Address & ", value is : " & cell.Value)
        End If
    Next cell
End Sub

Re: Determine Values In A Range Are Numeric

I just realized that there are 2 non-numeric exceptions I do not want a messagebox to apper on: the words BASE and A/W.
How could I add this to the IF statement below to skip over those 2 situations? (an example below is what I'm thinking of)

For Each i In Range("Range")
If Not IsNumeric(i) And (i<>"BASE" or i<>"A/W") Then

Quote from WinteE

This code should do the job :

Code

Sub FindNonNumerics()


For Each i In Range("Range")
    If Not IsNumeric(i) Then
        MsgBox ("A non numeric item has been found, it's value is : " & i.Value)
    End If
Next i


End Sub

Display More

Erik

Re: Determine Values In A Range Are Numeric

That did seem to work thank you!

Quote from WinteE

This code should do the job :

Code

Sub FindNonNumerics()


For Each i In Range("Range")
    If Not IsNumeric(i) Then
        MsgBox ("A non numeric item has been found, it's value is : " & i.Value)
    End If
Next i


End Sub

Display More

Erik

Re: Determine Values In A Range Are Numeric

Sorry I was not being clear at the end.
Show a messagebox giving the alpha value it found and the cell it was in.
I don't require an ELSE after the IF...just a simple IF .. END IF

Quote from Kenneth Hobson

I don't understand the last part. It shows a MsgBox if a cell is not numeric, but then what?

Of course users can do a Ctrl+G and Special to find such things.

I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.
Any suggestions?

Re: If Found Function/logic To Paste

Thank you very much! I replaced the Msgbox command with my copy and paste commands and it was successful!

Re: Copying A Variable Range Of Cells

For some reason it would select the cell that it offset you instead of the range.
I guess me using Excel 2000 has no relevance?

Quote from Derk

Glad you got it working. However, you remarked that [vba]ActiveCell.Resize(32, 11).Copy[/vba] did not work. I tested your posted code with the change to use resize and it worked on a small test case. Why did it not work for you?

Re: If Found Function/logic To Paste

anyone can help me? I would greatly appreciate it.

I am creating a macro that will be copying values from one worksheet and pasting them in one of two possible locations into another worksheet - depending on a title for my data. The title I will search on is located in worksheet "Sheet1".
If I find the title "WESTERN PERSPECTIVE" in Sheet1, I want to copy a range of values into one place on the other worksheet "Sheet2", otherwise if I find the title "EASTERN PERSPECTIVE" I want to copy a range of values into the 2nd place on my other worksheet "Sheet2".

My logic should follow:
If found "WESTERN PERSPECTIVE" in Sheet1, paste values into area 1 of Sheet2, else do nothing.
If found "EASTERN PERSPECTIVE" in Sheet1, paste values into area 2 of Sheet2, else do nothing.

I do not require coding for the copying and pasting, just the IF logic based on Found fields.

I do have the coding which selects the field if the 'Find' is successful:

Cells.Find(What:="WESTERN PERSPECTIVE", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate

Re: Copying A Variable Range Of Cells

Robert B's suggestion helped, thank you everyone.
Below is the code that worked, I simply altered Robert's range suggestion:

Sub FindRegGrd()


    Cells.Find(What:="Regular Ground", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate


    ActiveCell.Offset(8, 9).Select
    
    Range(ActiveCell, ActiveCell.Offset(32, 11)).Select


    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
End Sub

Re: Copying A Variable Range Of Cells

After trying the resize property, it still has the active cell selected where I started from.
Below is my code. First I did a text search, then I offset 8 rows and 9 columns as this is always where I need to copy from the text. From that cell (which varies between worksheets) I need to copy 32 down and 11 across.

Sub FindRegGrd()


    Cells.Find(What:="Regular Ground", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate


    ActiveCell.Offset(8, 9).Select
    
'    ActiveCell.Resize(32, 11).Copy - did not work 
'this code below will follow after I select the range


    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
End Sub

Quote from Derk

Try
[vba]ActiveCell.Resize(32, 11).Copy
[/vba]

I am creating a macro needed for many worksheets.
I am trying to paste a range that will be different within each worksheet.
I started the macro off by locating the correct cell to start the copy from.
I want to copy 11 columns across and 32 rows down from the cell I selected.
Can someone please help me out?