IF else statement

  • Thread starter Thread starter ims121uk
  • Start date Start date
I

ims121uk

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
 
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
 
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)
 
much neater :-)
--
Allllen


Bob Phillips said:
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)
Click to expand...
 
Many thanks for your help people, but i am still having problems with
this code, I think it may be the sheets.
 
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.
 
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
 
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?
 
Yeah that what i want the code to do.
Can i have look at your code for this example?

Cheers

Imran said:
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


[email protected] said:
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
Click to expand...
Click to expand...
 
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


[email protected] said:
Yeah that what i want the code to do.
Can i have look at your code for this example?

Cheers

Imran said:
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


[email protected] said:
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


:

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


:

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)

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Are all 5 worksheets in the same workbook?

Mike F
Yeah that what i want the code to do.
Can i have look at your code for this example?

Cheers

Imran said:
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


[email protected] said:
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


:

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


:

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)

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
many thanks Allen,

I got it working now totally forgot about module run as marco.

many thanks
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


[email protected] said:
Yeah that what i want the code to do.
Can i have look at your code for this example?

Cheers

Imran said:
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


:

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


:

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


:

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)

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicating worksheet based on range and changing worksheet name 3
Counting Work Days 5
Delete Rows as per Text, Font Color, Font Type, Time Format 11
Code condition 3
Copy data from one sheet to another 4
Copy code failing 2
Linking Cells 2
Hidden sheet bleeds through 3

Back
Top