|
IF else statement
I am having problem constructing a statement that would copy multiple
cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Sub TryThis()
If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
The OP used ElseIf, so assuming that is wanted
With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
much neater :-)
-- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Many thanks for your help people, but i am still having problems with
this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
A few questions so we can try to help you:
1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
I have changed the sheets names and insert an module for the code
doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Well, I have set up a new book with your sheet names and it works fine for me.
Maybe there is a gap between what you want and what we have provided. We have given you something that looks in [sheet "SEO" cell B3], and if it finds something there, it will write that into [sheet "Summary" cell B10]. Otherwise it will see if there is something in [sheet "E01" cell B3] and if it finds something there is will write it into [sheet "Summary" cell B10]. etc etc Did I misunderstand and maybe you wanted something else? -- Allllen " wrote: I have changed the sheets names and insert an module for the code doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Yeah that what i want the code to do.
Can i have look at your code for this example? Cheers Imran Allllen wrote: Well, I have set up a new book with your sheet names and it works fine for me. Maybe there is a gap between what you want and what we have provided. We have given you something that looks in [sheet "SEO" cell B3], and if it finds something there, it will write that into [sheet "Summary" cell B10]. Otherwise it will see if there is something in [sheet "E01" cell B3] and if it finds something there is will write it into [sheet "Summary" cell B10]. etc etc Did I misunderstand and maybe you wanted something else? -- Allllen " wrote: I have changed the sheets names and insert an module for the code doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Well I just used exactly the same code below which you posted yourself so
there is no problem there Things to check. 1 - your sheet names are definitely Summary, SEO, EO1, EO2, TM 2 - how to put the code into a module: (see here under "copy code to a regular module") http://www.contextures.com/xlvba01.html 3 - you have got some text in cell B3 in one of those sheets SEO, EO1, EO2, TM 4 - to run the code you need to do Tools Macro Macros and Run the code called DataB3 -- Allllen " wrote: Yeah that what i want the code to do. Can i have look at your code for this example? Cheers Imran Allllen wrote: Well, I have set up a new book with your sheet names and it works fine for me. Maybe there is a gap between what you want and what we have provided. We have given you something that looks in [sheet "SEO" cell B3], and if it finds something there, it will write that into [sheet "Summary" cell B10]. Otherwise it will see if there is something in [sheet "E01" cell B3] and if it finds something there is will write it into [sheet "Summary" cell B10]. etc etc Did I misunderstand and maybe you wanted something else? -- Allllen " wrote: I have changed the sheets names and insert an module for the code doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
Are all 5 worksheets in the same workbook?
Mike F wrote in message ups.com... Yeah that what i want the code to do. Can i have look at your code for this example? Cheers Imran Allllen wrote: Well, I have set up a new book with your sheet names and it works fine for me. Maybe there is a gap between what you want and what we have provided. We have given you something that looks in [sheet "SEO" cell B3], and if it finds something there, it will write that into [sheet "Summary" cell B10]. Otherwise it will see if there is something in [sheet "E01" cell B3] and if it finds something there is will write it into [sheet "Summary" cell B10]. etc etc Did I misunderstand and maybe you wanted something else? -- Allllen " wrote: I have changed the sheets names and insert an module for the code doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
IF else statement
many thanks Allen,
I got it working now totally forgot about module run as marco. many thanks Allllen wrote: Well I just used exactly the same code below which you posted yourself so there is no problem there Things to check. 1 - your sheet names are definitely Summary, SEO, EO1, EO2, TM 2 - how to put the code into a module: (see here under "copy code to a regular module") http://www.contextures.com/xlvba01.html 3 - you have got some text in cell B3 in one of those sheets SEO, EO1, EO2, TM 4 - to run the code you need to do Tools Macro Macros and Run the code called DataB3 -- Allllen " wrote: Yeah that what i want the code to do. Can i have look at your code for this example? Cheers Imran Allllen wrote: Well, I have set up a new book with your sheet names and it works fine for me. Maybe there is a gap between what you want and what we have provided. We have given you something that looks in [sheet "SEO" cell B3], and if it finds something there, it will write that into [sheet "Summary" cell B10]. Otherwise it will see if there is something in [sheet "E01" cell B3] and if it finds something there is will write it into [sheet "Summary" cell B10]. etc etc Did I misunderstand and maybe you wanted something else? -- Allllen " wrote: I have changed the sheets names and insert an module for the code doesn't still seem to work. There are no errors on the code and also did a debug nothing was found. Have a look at the code: - Sub DataB3() With Sheets("Summary").Range("B10") If Worksheets("SEO").Range("B3") < "" Then .Value = Worksheets("SEO").Range("B3").Value ElseIf Worksheets("EO1").Range("B3") < "" Then .Value = Worksheets("EO1").Range("B3").Value ElseIf Worksheets("EO2").Range("B3") < "" Then .Value = Worksheets("EO2").Range("B3").Value ElseIf Worksheets("TM").Range("B3") < "" Then .Value = Worksheets("TM").Range("B3").Value End If End With End Sub Cheers Imran Allllen wrote: A few questions so we can try to help you: 1 - what are your sheets called? Then we can adjust Bob's neat code to fit your sheet names. 2 - are you sure that you know where you are putting the code? (should be in a module within the VBE) 3 - if you are getting errors or things aren't happening as you expect, tell us what you are seeing and what errors you are getting. -- Allllen " wrote: Many thanks for your help people, but i am still having problems with this code, I think it may be the sheets. Allllen wrote: much neater :-) -- Allllen "Bob Phillips" wrote: The OP used ElseIf, so assuming that is wanted With Sheets("Sheet5").Range("B6") If Worksheets("Sheet1").Range("B3") < "" Then .Value = Worksheets("Sheet1").Range("B3").Value ElseIf Worksheets("Sheet2").Range("B3") < "" Then .Value = Worksheets("Sheet2").Range("B3").Value ElseIf Worksheets("Sheet3").Range("B3") < "" Then .Value = Worksheets("Sheet3").Range("B3").Value ElseIf Worksheets("Sheet4").Range("B3") < "" Then .Value = Worksheets("Sheet4").Range("B3").Value End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allllen" wrote in message ... Sub TryThis() If Sheets("Sheet1").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet1").Range("B3").Value Else If Sheets("Sheet2").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet2").Range("B3").Value Else If Sheets("Sheet3").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet3").Range("B3").Value Else If Sheets("Sheet4").Range("B3") < "" Then Sheets("Sheet5").Range("B6").Value = Sheets("Sheet4").Range("B3").Value End If End If End If End If End Sub -- Allllen " wrote: I am having problem constructing a statement that would copy multiple cells from different worksheet that display a text output in one cell of one worksheet. For example: - sub if sheet 1 B3 has text "print in worksheet 5 B6" end if else if sheet 2 B3 has text "print in worksheet 5 B6" end if else if sheet 3 B3 has text "print in worksheet 5 B6" end if else if sheet 4 B3 has text "print in worksheet 5 B6" end if END |
| All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com