Posts by Simon Lloyd

Hi all i have a nested worksheet formula as below in box 2 (took some time to devise it!!!), the formula works fine but i need to add the line in box 1 to the statement, but i have read that there is a limit of 7 IF's to a statement anyone know a clever way around it?
Box1:

=If(And(N5=4,K2="Line Off"),"",If(And(F5=4,K2="Line Off"),"",If(And(B5=4,K2="Line Off"),"",If(And(R5=4,K2="Line Off"),"",If(And(V5=4,K2="Line Off"),""

Box2:

=If(And(N5=4,K2="No Ops"),"",If(And(F5=4,K2="No Ops"),"",If(And(B5=4,K2="No Ops"),"",If(And(R5=4,K2="No Ops"),"",If(And(V5=4,K2="No Ops"),"",If(K2="Line Off",B33,If(K2="No Ops",B33)))))))

Hope you can help!

Regards,
Simon

Re: Disable Update Links Message

You could put this in your workbook in the ThisWorkBook module, this way you never have the question asked!

Private Sub Workbook_Open()    
Workbooks.Open FileName:= _
        "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksNever
End Sub

just exchange the path of your workbook and workbook name.

Regards,
Simon

Re: Format Copy From One Sheet To Another

Welcome to Ozgrid!, do you want just the format copied over or do you want the contents?, do you want this to run every time a cell is changed or just once when you have built the grid?, can you post a sample of your workbook?

Regards,
Simon

Re: Worksheet Formula If Statements With Many Arguments?

ByTheCringe..............Excellent!

Thanks!

Regards,
Simon

Re: Hiding Columns Macro

if you want to hide columns in a macro you could use this

Columns("C:E").Select
        Selection.EntireColumn.Hidden = True

where this would hide columns C to E or you could use something like to hide non contiguous columns

Range("C:C,E:E,I:I").Select

to unhide the columns you could have the column selection again and use this to unhide them.

Selection.EntireColumn.Hidden = False

Really though if you start your macro recorder select the columns you want to hide then choose Format | Columns | Hide then stop your recorder you will have what you need.

Regards,
Simon

Hi all i have 2 worksheet function IF statements that of course look for certain conditions, but in some instances i need to combine the IF statements in one cell, the 2 i need to combine are below:

=IF(D3="S","Sick",IF(D3="SW","Swapped",Sheet2!B3))

=IF(OR(C1="Line On",G1="Line On"),Sheet2!B3,"")

so what i need is for the cell to show either Sick, Swapped or the contents of Sheet2!B3 however if both C1 and G1 show Line Off then cell must be blank, which is what i achieve with the second if statement.

All help much appreciated!

Regards,
Simon

Re: Faulty Macro

Gary i haven't looked at your example but if you check this thread out there are examples and solutions to conditional formatting via vba.

Regards,
Simon

http://www.ozgrid.com/forum/showthread.php?t=59414

Re: Unfinished Workbook Before Save

How large is your range in K & L? try adapting this

Private Sub Workbook_BeforeClose(Cancel As Boolean)


Dim Rng
Dim MyCell
Set Rng = Sheets("Sheet1").Range("K1:L100")
For Each MyCell In Rng
If MyCell = "" Then
MsgBox "You have some unanswered boxes in columns K and  L at " & MyCell.Address, vbOKOnly, "Close Prohibited"
Cancel = True
End If
Next
End Sub

you would drop this in to the ThisWorkBook module, if a blank appears in any cell in the specified range then it will warn them and let them know which cell.

Regards,
Simon

Re: Typing In Comboboxs

Joe i'm not sure but in the properties menu under catagorised you could use

0-fmMatchEntryFirstLetter

add a blank to the combobox list and also choose

MatchRequired True

this way it will either match a blank if the box is blank or match what is chosen from the list.......well thats my theory!

Hope it helps.

Regards,
Simon

Re: Duplicate Records

Hi this is an article found on MS knowledge base

Quote

Enter the following code in a module sheet:

Sub Find_Matches()


          Dim CompareRange As Variant, x As Variant, y As Variant


          ' Set CompareRange equal to the range to which you will
          ' compare the selection.


          Set CompareRange = Range("C1:C5")
          ' NOTE: If the compare range is located on another workbook
          ' or worksheet, use the following syntax.
          ' Set CompareRange = Workbooks("Book2"). _
          '   Worksheets("Sheet2").Range("C1:C5")
          '
          ' Loop through each cell in the selection and compare it to
          ' each cell in CompareRange.


          For Each x In Selection
              For Each y In CompareRange
                  If x = y Then x.Offset(0, 1) = x
              Next y
          Next x


      End Sub

Quote

2. In a new worksheet, enter the following data (leave column B empty):
A1: 1 C1: 3
A2: 2 C2: 5
A3: 3 C3: 8
A4: 4 C4: 2
A5: 5 C5: 0

3. Select the range A1:A5.
4. On the Tools menu, click Macro, and select the macro "Find_Matches". (In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, click the Tools menu, point to Macro, and click Macros. Then, select the macro "Find_Matches".) Click Run to run the macro.
The duplicate numbers are displayed in column B. The matching numbers will be put next to the first column as illustrated here:
A1: 1 B1: C1: 3
A2: 2 B2: 2 C2: 5
A3: 3 B3: 3 C3: 8
A4: 4 B4: C4: 2
A5: 5 B5: 5 C5: 0

Display More

used like this it will find and mark each of your duplicates, you should then be able to write a shortroutine to remove entire rows that have data in column B

Regards,
Simon

Re: Unfinished Workbook Before Save

Where are the answers going to be stored when the question is answered i.e which cell?, which cells have the questions in? You say workbook.....how many worksheets have questions in K and L that need answering?

Regards,
Simon

Re: Launch A Userform During Startup

Excel has two special subs one is Auto_Open and the other Auto_close, using these words as your sub in a module will cause them to be run when the workbook is opened like this

Sub Auto_Open()
Userform1.show
End Sub

this is provided your userform is called userform1, you can also use the workbook open event in the ThisWorkBook module and use

userform1.show

Regards,
Simon

Re: Opening Word Template Via Vba?

Norie, thanks for the reply again, how do i get the paste to occur 3 lines down from the header?

Regards,
Simon

Re: Opening Word Template Via Vba?

Norie thanks for the quick response!, i'm not sure at all when it comes to manipulating Word, in my code i do have this line

With appwd 
        .documents.Add 
        .Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab

but i use this because when i GetObject or CreateObject there is no documnet sheet so the .Add opens a new page, but like i said i am lost when using vba to control word!

Regards,
Simon

Hi all i have the code below that opens a word document and then pastes the contents of selected cells in to it, the code works fine, but what i would like to be able to do is open a word template called "Data Letter" stored in My Documents and then paste the cells contents 3 lines down from the header, any ideas?

Sub wrdstart()
Dim appwd As Object
On Error GoTo notloaded
Set appwd = GetObject(, "Word.Application")
notloaded:
    If Err.Number = 429 Then
        Set appwd = CreateObject("Word.Application")
    End If
appwd.Visible = True
On Error GoTo 0
With appwd
.documents.Add
.Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab
Range("A1:A5").Copy


.Selection.Paste


End With
End Sub

Regards,
Simon

Re: Transfer Data From One Sheet To Another

Billy, I know you have probably checked but have a look at your range by choosing these from your menu bar Insert | Names | Define, now click the name of the range you want to check once, at the bottom of the pop up box you will see the range appear for that name, click in this box once and you will be transported to that range and dotted selection box will surround the range, you may then use your scroll bars to take a look at the whole range to make sure it actually encompasses what you want, when you have done close the Names box.

Regards,
Simon

Re: Cut Move Range To New Sheet

Jennie try

ActiveSheet.Range(A65536).End(xlUp)).Select

or you could end it with .OffSet(x, x) and then select, where x is the rows or columns you want to move.

Regards,
Simon

Re: Worksheet Function Nested If Shows True Or False?

Thanks for your response Shades, ByTheCringe answered with

Quote from ByTheCringe

Do you want

=If(B3=11,"Sick",If(C1="Line Off","",Sheet2!B2))

which is exactly what i was trying to achieve!

Thansk very much both.

Regards,
Simon

Hi all i am trying to create a nested IF but i dont usually use worksheet functions, the formula i have will show either true or false but not the values i am trying to return.

=(IF(B3=11,"Sick",Sheet2!B2)=IF(C1="Line Off","",Sheet2!B2))

Could someone help re-arrange it please and explain what i have done wrong? Regards,
Simon

Re: Displaying More Than One Result In A Message Box?

Thanks for the clearing those issues up and again thanks for the mods!

Regards,
Simon