Posts by DeafnDumb

Re: How to get part of a cells information

Thanks guys for your help StephenR your code worked, Very much appreciated =)

I code that looks down column E if it matches "L" then it add the "L" to the information in the adjacent cell. Then it takes that and matches it to info in column CR of another sheet. Here is where I need the help. once it finds the match I need it to copy the information that is 4 columns over from column CR but same row and bring it back and paste it on the first sheet 3 columns over but same row from where it got the match criteria.

If UCase(Cells(i, 5).Value) = "L" Then
        Angle = (Cells(i, 5).Value & Cells(i, 6).Value)
                 
                If Not IsError(Application.Match(Cells(i, 5).Value & Cells(i, 6).Value, Sheets(11).Range("cr3:cr144"), 0)) Then
                    
                    With Worksheets("sheet11").Range("cr3:cr144")
                    On Error Resume Next
                    Set afind = .Cells.Find(what:=X, lookat:=xlWhole)
                    Idx = afind.Offset(0, -4).Value
                    MsgBox (Angle & " it a match found on row" & Idx)
                    End With
                Cells(i, 9).Value = idx
                Else
                MsgBox (Angle & " No Match in data base correct and re-sort")
                End If
  
    End If

Any help would be greatly appreciated =)

I have a column that has information like 1x25, 12x28, 12x11.5, 112x211. I am trying to get the information left and right of the "x". the code I have below only works correctly if there is 2 numbers left and right of the x with no decimal.
My vba skills are limited. And any help would be greatly appreciated.

If UCase(Cells(i, 5).Value) = "C" Then
        variable = Left(Cells(i, 6), InStr(Cells(i, 6), "x") - 1)
        variable2 = Right(Cells(i, 6), InStr(Cells(i, 6), "x") - 1)
        MsgBox (variable)
        MsgBox (variable2)
        Cells(i, 9).Value = (variable2 * 12 * variable * (490 / 1728))
    End If

Re: Check If Certain Characters Are Used

Thank you it does what i needed

Is there a way to check if an enrty to a textbox on a user form has any of the following / \ ? * [ ] ? if so then a msgbox tells the user they cant use any of those characters?

Thanks in advance for any help =)

I have been looking at Dave's Script found in this post
http://www.ozgrid.com/forum/showthread.php?t=53520
It works just like i want it too. But when I try and use it in my workbook with differnt sheet names it doesnt work. I have even tryed to rename the sheets in the example file and it stops working. Can some one help me. My sheets are as follows:

Sheet1 (JI)
Sheet2 (GC)
Sheet3 (ET)
Sheet4 (SV)
Sheet5 (JB)
Sheet6 (P)
Sheet8 (Info Sheet) <----This is where the Notice to turn on Marcos is
Sheet9 (D)

I have the following code in Module2

Public bIsClosing As Boolean
Dim wsSheet As Worksheet


Sub HideAll()


Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.CodeName = "Sheet8" Then
     wsSheet.Visible = xlSheetVisible
    Else
     wsSheet.Visible = xlSheetVeryHidden
    End If
Next wsSheet
Application.ScreenUpdating = True


End Sub


Sub ShowAll()
bIsClosing = False


For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.CodeName <> "Sheet8" Then
     wsSheet.Visible = xlSheetVisible
    End If
Next wsSheet
Sheet2.Visible = xlSheetHidden
Sheet3.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
Sheet8.Visible = xlSheetVeryHidden
End Sub

And the following code in ThisWorkbook

Private Sub Workbook_Open()
Run "showAll"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 bIsClosing = True
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cancel = True Or bIsClosing = False Then Exit Sub
Run "HideAll"
End Sub




Private Sub Workbook_Deactivate()
If bIsClosing = False Then Exit Sub
Run "HideAll"
End Sub

Thanks in Advance

What I have is 2 workbooks. Workbook1 (fixed name) has an Workbook_Open() script that needs to be halted if workbook1 is opened by workbook2(variable name). I’m not sure if this can be done. Thanks in advance

Re: Open A Closed Workbook Enter Info Find Last Row Paste Info Then Close

Yes that work for finding the last row Thank You So much. Is it possible when the closed workbook opens and the msgbox pops up to automatically enter in the word "template" and automatically click ok?

What i need to do is Open a Closed workbook (which i have done in the code below)

1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)

2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work that why i am asking for help. The red is where it hangs up. Thanks in advance for any help.

openpath = ActiveWorkbook.Path & "\"
        Set wb = Workbooks.Open(openpath & "Template.xls")
        With wb.Sheets("Contractor Info")
           [COLOR="Red"].Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select[/COLOR]
            ActiveCell.Value = Msg1
            ActiveCell.Offset(0, 4).Value = Msg2
            ActiveCell.Offset(0, 5).Value = Msg3
            ActiveCell.Offset(0, 3).Value = Msg5
            ActiveCell.Offset(0, 2).Value = Msg4
            ActiveCell.Offset(0, 1).Value = Msg6
            ActiveCell.Offset(0, 6).Value = Msg7
            Application.Calculation = xlCalculationManual
                 Sheets("Contractor Info").Range("B5:h200").Sort Key1:=Sheets("Contractor Info").Range("B3"), Order1:=xlAscending, _
                 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
                Application.Calculation = xlCalculationAutomatic
          End With
     
    wb.Close , True
End Sub

Re: Select Certain Cells In The Same Column To Filter

works just like i needed it to THANKS ALOT!!!!!! =)

Re: Select Certain Cells In The Same Column To Filter

I need it to filter all at the same time. This is a filter just before we print.
Is there a way to do it using something other than filter?

Re: Select Certain Cells In The Same Column To Filter

I tried this but it stops in the first range. I must be doing something wrong then

Private Sub CommandButton1_Click()
Range("c36:c50").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("c54:c68").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("c72:c87").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("c91:c155").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("c158:c172").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("c176:c202").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

How do you select certain ranges in the same column to filter?

The ranges I need are
"c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202"

all filtering for blanks

I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.

Any help will be greatly appreciated =)

Re: Copy Part Of A Column To Another Column On Differnt Worksheet

Sorry that is my fault again your right it does work and it does what i want.
I didnt catch the 2. Thanks for all your help =)

Re: Copy Part Of A Column To Another Column On Differnt Worksheet

yes thats what i want but thats not the result it posts it at the last used cell in row 1

Re: Copy Part Of A Column To Another Column On Differnt Worksheet

sorry the error was on my part... But it still does the same thing as above it still finds the last column used by the first row not the second.

Re: Copy Part Of A Column To Another Column On Differnt Worksheet

now it just gives me an error

Re: Copy Part Of A Column To Another Column On Differnt Worksheet

yes it helps it does work...but it doesnt look for the next avaible column starting at row 2 it is looking at row 1 is there a way to change that? ie row 1 has header list from A to S and when it copys over it pasted it at T what i needed was it to post it a C since that is the first empy column starting at row 2...I hope that makes since

Hi,

I have column t6:t23 on sheet one that i want to copy and paste to the next available column in sheet2 but it has to start looking for the next available column starting at at row2 because row 1 has column header.

I know this can be done with (xltoright) just not sure how to do it

Thanks in advance for any help =)

Is there a way with a formula to have a result of one cell pasted in a different cell? ie.

C1's formula =if(and(isnumber(a1),isnumber(b1)),b1-a1,if(and(isnumber(a1),isblank(b1)),[COLOR="Red"] b1 = d1[/COLOR],"")

Quote

A B C D
12/18/06 formula above =today()

if b1 isbalnk it will copy the value of d1 to b1

I dont know if this can be done i have looked at offset and a few others but it doesnt look like this can be done but want to see if i have over looked domething