# Posts by Simon Lloyd

• ## Multiple Nested If Formulas: Greater Than 7

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:

Code
``=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:

Code
``=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

• ## Disable Update Links Message

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

Code
``````Private Sub Workbook_Open()
Workbooks.Open FileName:= _
End Sub``````

just exchange the path of your workbook and workbook name.

Regards,
Simon

• ## Copy Formats Between Worksheets

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

• ## Combine Multiple If Formulas

Re: Worksheet Formula If Statements With Many Arguments?

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

Thanks!

Regards,
Simon

• ## Hide Columns Macro

Re: Hiding Columns Macro

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

Code
``````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

Code
``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.

Code
``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

• ## Combine Multiple If Formulas

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:

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

Code
``=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

• ## Stop File Closing If Ranges Blank

Re: Unfinished Workbook Before Save

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

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

• ## Typing In Comboboxs

Re: Typing In Comboboxs

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

Code
``0-fmMatchEntryFirstLetter``

add a blank to the combobox list and also choose

Code
``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

• ## Eliminate Duplicate Records

Re: Duplicate Records

Hi this is an article found on MS knowledge base

Quote

Enter the following code in a module sheet:

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

• ## Stop File Closing If Ranges Blank

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

• ## Launch A Userform During Startup

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

Code
``````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

Code
``userform1.show``

Regards,
Simon

• ## Opening Word Template Via Macro

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

• ## Opening Word Template Via Macro

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

Code
``````With appwd
.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

• ## Opening Word Template Via Macro

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?

Regards,
Simon

• ## Transfer Data From One Sheet To Another

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

• ## Cut Move Range To New Sheet

Re: Cut Move Range To New Sheet

Jennie try

Code
``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

• ## IF Formula Returns True or False, Not Desired Results

Re: Worksheet Function Nested If Shows True Or False?

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

• ## IF Formula Returns True or False, Not Desired Results

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.

Code
``=(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

• ## Display Many Cell Result In Message Box

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