IF..Then..ELSE.. ELSEIF

Hi NG,

I have to make a If then else statement.

I have 3 cells that may contain something like this (text):

exampel1:
Cell1("B47"): peter
Cell2("B48"): Blank
Cell3("B49"): Blank

exampel2:
Cell1("B47"): thomas
Cell2("B48"): michael
Cell3("B49"): Blank

exampel3:
Cell1("B47"): thomas
Cell2("B48"): peter
Cell3("B49"): michell

And here is what i want to happen, the cells with names should be copied to
another sheed like this:

exampel1: peter

exampel2: thomas / michael

exampel3: thomas / peter / michell

And here is one of me solutions that wont work:

If Worksheets(2).Range("B48") = "Blank" Then
bk.Worksheets(2).Range("D6").Value = _
Format(Range("B47").Value) ' kopier 1 folietyper
End If
If Worksheets(2).Range("B49") = "Blank" Then
bk.Worksheets(2).Range("D6").Value = _
Format(Range("B47").Value) & " / " & Format(Range("B48").Value) '
indsætter 2 folietyper
Else: bk.Worksheets(2).Range("D6"). _
Value = Format(Range("B47").Value) & " / " & Format(Range("B48").Value) & "
/ " & Format(Range("B49").Value) ' kopier alle 3 folietyper
End If

When it only should copy B47 in exampel1 it copies B47 and B48.

AHA.

Ole_

Dim cnt as Long, sStr as String
Dim cell as Range
cnt = Application.CountA(Worksheets(2).Range("B47:B49"))

for each cell in Worksheets(2).Range("B47").Resize(cnt,1)
sStr = sStr & cell & "/"
Next
sStr = left(sStr,len(sStr)-1)
bk.Worksheets(2).Range("D6").Value = sStr

--
Regards,
Tom Ogilvy


"ole_" <dontmail@me.dk> wrote in message
news:uEl%239iORFHA.2356@TK2MSFTNGP14.phx.gbl...
> Hi NG,
>
> I have to make a If then else statement.
>
> I have 3 cells that may contain something like this (text):
>
> exampel1:
> Cell1("B47"): peter
> Cell2("B48"): Blank
> Cell3("B49"): Blank
>
> exampel2:
> Cell1("B47"): thomas
> Cell2("B48"): michael
> Cell3("B49"): Blank
>
> exampel3:
> Cell1("B47"): thomas
> Cell2("B48"): peter
> Cell3("B49"): michell
>
> And here is what i want to happen, the cells with names should be copied
to
> another sheed like this:
>
> exampel1: peter
>
> exampel2: thomas / michael
>
> exampel3: thomas / peter / michell
>
> And here is one of me solutions that wont work:
>
> If Worksheets(2).Range("B48") = "Blank" Then
> bk.Worksheets(2).Range("D6").Value = _
> Format(Range("B47").Value) ' kopier 1 folietyper
> End If
> If Worksheets(2).Range("B49") = "Blank" Then
> bk.Worksheets(2).Range("D6").Value = _
> Format(Range("B47").Value) & " / " & Format(Range("B48").Value) '
> indsætter 2 folietyper
> Else: bk.Worksheets(2).Range("D6"). _
> Value = Format(Range("B47").Value) & " / " & Format(Range("B48").Value) &
"
> / " & Format(Range("B49").Value) ' kopier alle 3 folietyper
> End If
>
> When it only should copy B47 in exampel1 it copies B47 and B48.
>
> AHA.
>
> Ole_
>
>

Hi Tom,

It works, but not the rigth way, it copies all 3 cells to D6, maybe i did
not explain
it correctly, the "Blank" is sort of a name and not a empty cell.

Regards,
Ole

"Tom Ogilvy" <twogilvy@msn.com> skrev i en meddelelse
news:eK4U$8ORFHA.508@TK2MSFTNGP12.phx.gbl...
> Dim cnt as Long, sStr as String
> Dim cell as Range
> cnt = Application.CountA(Worksheets(2).Range("B47:B49"))
>
> for each cell in Worksheets(2).Range("B47").Resize(cnt,1)
> sStr = sStr & cell & "/"
> Next
> sStr = left(sStr,len(sStr)-1)
> bk.Worksheets(2).Range("D6").Value = sStr
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ole_" <dontmail@me.dk> wrote in message
> news:uEl%239iORFHA.2356@TK2MSFTNGP14.phx.gbl...
> > Hi NG,
> >
> > I have to make a If then else statement.
> >
> > I have 3 cells that may contain something like this (text):
> >
> > exampel1:
> > Cell1("B47"): peter
> > Cell2("B48"): Blank
> > Cell3("B49"): Blank
> >
> > exampel2:
> > Cell1("B47"): thomas
> > Cell2("B48"): michael
> > Cell3("B49"): Blank
> >
> > exampel3:
> > Cell1("B47"): thomas
> > Cell2("B48"): peter
> > Cell3("B49"): michell
> >
> > And here is what i want to happen, the cells with names should be copied
> to
> > another sheed like this:
> >
> > exampel1: peter
> >
> > exampel2: thomas / michael
> >
> > exampel3: thomas / peter / michell
> >
> > And here is one of me solutions that wont work:
> >
> > If Worksheets(2).Range("B48") = "Blank" Then
> > bk.Worksheets(2).Range("D6").Value = _
> > Format(Range("B47").Value) ' kopier 1 folietyper
> > End If
> > If Worksheets(2).Range("B49") = "Blank" Then
> > bk.Worksheets(2).Range("D6").Value = _
> > Format(Range("B47").Value) & " / " & Format(Range("B48").Value) '
> > indsætter 2 folietyper
> > Else: bk.Worksheets(2).Range("D6"). _
> > Value = Format(Range("B47").Value) & " / " & Format(Range("B48").Value)
&
> "
> > / " & Format(Range("B49").Value) ' kopier alle 3 folietyper
> > End If
> >
> > When it only should copy B47 in exampel1 it copies B47 and B48.
> >
> > AHA.
> >
> > Ole_
> >
> >
>
>

Hi,

I've got it, i have as you can see in the subject placed the ELSE before
ELSEIF
and then it would not work.

But now it works.

Thanks.

"ole_" <dontmail@me.dk> skrev i en meddelelse
news:OedhRDYRFHA.3972@TK2MSFTNGP14.phx.gbl...
> Hi Tom,
>
> It works, but not the rigth way, it copies all 3 cells to D6, maybe i did
> not explain
> it correctly, the "Blank" is sort of a name and not a empty cell.
>
> Regards,
> Ole
>
> "Tom Ogilvy" <twogilvy@msn.com> skrev i en meddelelse
> news:eK4U$8ORFHA.508@TK2MSFTNGP12.phx.gbl...
> > Dim cnt as Long, sStr as String
> > Dim cell as Range
> > cnt = Application.CountA(Worksheets(2).Range("B47:B49"))
> >
> > for each cell in Worksheets(2).Range("B47").Resize(cnt,1)
> > sStr = sStr & cell & "/"
> > Next
> > sStr = left(sStr,len(sStr)-1)
> > bk.Worksheets(2).Range("D6").Value = sStr
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ole_" <dontmail@me.dk> wrote in message
> > news:uEl%239iORFHA.2356@TK2MSFTNGP14.phx.gbl...
> > > Hi NG,
> > >
> > > I have to make a If then else statement.
> > >
> > > I have 3 cells that may contain something like this (text):
> > >
> > > exampel1:
> > > Cell1("B47"): peter
> > > Cell2("B48"): Blank
> > > Cell3("B49"): Blank
> > >
> > > exampel2:
> > > Cell1("B47"): thomas
> > > Cell2("B48"): michael
> > > Cell3("B49"): Blank
> > >
> > > exampel3:
> > > Cell1("B47"): thomas
> > > Cell2("B48"): peter
> > > Cell3("B49"): michell
> > >
> > > And here is what i want to happen, the cells with names should be
copied
> > to
> > > another sheed like this:
> > >
> > > exampel1: peter
> > >
> > > exampel2: thomas / michael
> > >
> > > exampel3: thomas / peter / michell
> > >
> > > And here is one of me solutions that wont work:
> > >
> > > If Worksheets(2).Range("B48") = "Blank" Then
> > > bk.Worksheets(2).Range("D6").Value = _
> > > Format(Range("B47").Value) ' kopier 1 folietyper
> > > End If
> > > If Worksheets(2).Range("B49") = "Blank" Then
> > > bk.Worksheets(2).Range("D6").Value = _
> > > Format(Range("B47").Value) & " / " & Format(Range("B48").Value) '
> > > indsætter 2 folietyper
> > > Else: bk.Worksheets(2).Range("D6"). _
> > > Value = Format(Range("B47").Value) & " / " &
Format(Range("B48").Value)
> &
> > "
> > > / " & Format(Range("B49").Value) ' kopier alle 3 folietyper
> > > End If
> > >
> > > When it only should copy B47 in exampel1 it copies B47 and B48.
> > >
> > > AHA.
> > >
> > > Ole_
> > >
> > >
> >
> >
>
>

You don't have any ELSEIF in the code you posted.

Guess you were talking about two different things. You will find that most
people here are not clairvoyant.

--
Regards,
Tom Ogilvy


"ole_" <dontmail@me.dk> wrote in message
news:OXl$y5ZRFHA.2932@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I've got it, i have as you can see in the subject placed the ELSE before
> ELSEIF
> and then it would not work.
>
> But now it works.
>
> Thanks.
>
> "ole_" <dontmail@me.dk> skrev i en meddelelse
> news:OedhRDYRFHA.3972@TK2MSFTNGP14.phx.gbl...
> > Hi Tom,
> >
> > It works, but not the rigth way, it copies all 3 cells to D6, maybe i
did
> > not explain
> > it correctly, the "Blank" is sort of a name and not a empty cell.
> >
> > Regards,
> > Ole
> >
> > "Tom Ogilvy" <twogilvy@msn.com> skrev i en meddelelse
> > news:eK4U$8ORFHA.508@TK2MSFTNGP12.phx.gbl...
> > > Dim cnt as Long, sStr as String
> > > Dim cell as Range
> > > cnt = Application.CountA(Worksheets(2).Range("B47:B49"))
> > >
> > > for each cell in Worksheets(2).Range("B47").Resize(cnt,1)
> > > sStr = sStr & cell & "/"
> > > Next
> > > sStr = left(sStr,len(sStr)-1)
> > > bk.Worksheets(2).Range("D6").Value = sStr
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "ole_" <dontmail@me.dk> wrote in message
> > > news:uEl%239iORFHA.2356@TK2MSFTNGP14.phx.gbl...
> > > > Hi NG,
> > > >
> > > > I have to make a If then else statement.
> > > >
> > > > I have 3 cells that may contain something like this (text):
> > > >
> > > > exampel1:
> > > > Cell1("B47"): peter
> > > > Cell2("B48"): Blank
> > > > Cell3("B49"): Blank
> > > >
> > > > exampel2:
> > > > Cell1("B47"): thomas
> > > > Cell2("B48"): michael
> > > > Cell3("B49"): Blank
> > > >
> > > > exampel3:
> > > > Cell1("B47"): thomas
> > > > Cell2("B48"): peter
> > > > Cell3("B49"): michell
> > > >
> > > > And here is what i want to happen, the cells with names should be
> copied
> > > to
> > > > another sheed like this:
> > > >
> > > > exampel1: peter
> > > >
> > > > exampel2: thomas / michael
> > > >
> > > > exampel3: thomas / peter / michell
> > > >
> > > > And here is one of me solutions that wont work:
> > > >
> > > > If Worksheets(2).Range("B48") = "Blank" Then
> > > > bk.Worksheets(2).Range("D6").Value = _
> > > > Format(Range("B47").Value) ' kopier 1 folietyper
> > > > End If
> > > > If Worksheets(2).Range("B49") = "Blank" Then
> > > > bk.Worksheets(2).Range("D6").Value = _
> > > > Format(Range("B47").Value) & " / " & Format(Range("B48").Value) '
> > > > indsætter 2 folietyper
> > > > Else: bk.Worksheets(2).Range("D6"). _
> > > > Value = Format(Range("B47").Value) & " / " &
> Format(Range("B48").Value)
> > &
> > > "
> > > > / " & Format(Range("B49").Value) ' kopier alle 3 folietyper
> > > > End If
> > > >
> > > > When it only should copy B47 in exampel1 it copies B47 and B48.
> > > >
> > > > AHA.
> > > >
> > > > Ole_
> > > >
> > > >
> > >
> > >
> >
> >
>
>