Using "IF" Statement

[Scott Beavers]

I'm trying to create a form in Excel to sort from the form and take the data
to another worksheet.
I am very new to this and any help would be appreciated.

I have a value in a cell that will direct what sheet I want the cells copied
to, so I thought I could use an If statement but have been unable to right
the code properly, ie...

If "f2" = 1 Then
Sheets("XX1").S elect
Range("A2").Sel ect
ActiveSheet.Pas te

Else
If "f2" = 2 Then
Sheets("XX2").S elect
Range("A2").Sel ect
ActiveSheet.Pas te

Else
If "f2" = 3 Then
Sheets("XX3").S elect
Range("A2").Sel ect
ActiveSheet.Pas te

Else
If "f2" = 4 Then
Sheets("XX4").S elect
Range("A2").Sel ect
ActiveSheet.Pas te

Else
If "f2" = 5 Then
Sheets("XX5").S elect
Range("A2").Sel ect
ActiveSheet.Pas te

End If

[StoneWare]

Re: Using "IF&quo t; Statement


"Scott Beavers" <sbeavers@aasur veying.com> skrev i en meddelelse
news:p-KdnRsPr_34d6zfR Vn-uw@cmspan.net.. .[color=blue]
> I'm trying to create a form in Excel to sort from the form and take the
> data
> to another worksheet.
> I am very new to this and any help would be appreciated.
>
> I have a value in a cell that will direct what sheet I want the cells
> copied
> to, so I thought I could use an If statement but have been unable to right
> the code properly, ie...
>
> If "f2" = 1 Then
> Sheets("XX1").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 2 Then
> Sheets("XX2").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 3 Then
> Sheets("XX3").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 4 Then
> Sheets("XX4").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 5 Then
> Sheets("XX5").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> End If
>
>[/color]

A select case is better in this case

Select case "f2"
case 1
Sheets("XX1").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 2
Sheets("XX2").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 3
Sheets("XX3").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 4
Sheets("XX4").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 5
Sheets("XX5").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
End select


[color=blue]
>[/color]

[StoneWare]

Re: Using &quot;IF&quo t; Statement


"Scott Beavers" <sbeavers@aasur veying.com> skrev i en meddelelse
news:p-KdnRsPr_34d6zfR Vn-uw@cmspan.net.. .[color=blue]
> I'm trying to create a form in Excel to sort from the form and take the
> data
> to another worksheet.
> I am very new to this and any help would be appreciated.
>
> I have a value in a cell that will direct what sheet I want the cells
> copied
> to, so I thought I could use an If statement but have been unable to right
> the code properly, ie...
>
> If "f2" = 1 Then
> Sheets("XX1").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 2 Then
> Sheets("XX2").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 3 Then
> Sheets("XX3").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 4 Then
> Sheets("XX4").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 5 Then
> Sheets("XX5").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> End If
>
>[/color]

A select case is better in this case

Select case "f2"
case 1
Sheets("XX1").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 2
Sheets("XX2").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 3
Sheets("XX3").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 4
Sheets("XX4").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
case 5
Sheets("XX5").S elect
Range("A2").Sel ect
ActiveSheet.Pas te
End select


[color=blue]
>[/color]

[Steve Gerrard]

Re: Using &quot;IF&quo t; Statement


"Scott Beavers" <sbeavers@aasur veying.com> wrote in message
news:p-KdnRsPr_34d6zfR Vn-uw@cmspan.net.. .[color=blue]
> I'm trying to create a form in Excel to sort from the form and take the data
> to another worksheet.
> I am very new to this and any help would be appreciated.
>
> I have a value in a cell that will direct what sheet I want the cells copied
> to, so I thought I could use an If statement but have been unable to right
> the code properly, ie...
>
> If "f2" = 1 Then[/color]

The string "f2" is never equal to the integer 1 :-)

I assume what you mean is something more like

If ActiveSheet.Ran ge("F2").Value = 1 Then

Or perhaps better would be

vPick = ActiveSheet.Ran ge("F2").Value

If vPick = 1 Then

That way you don't get scrambled when the active sheet gets changed.

[Steve Gerrard]

Re: Using &quot;IF&quo t; Statement


"Scott Beavers" <sbeavers@aasur veying.com> wrote in message
news:p-KdnRsPr_34d6zfR Vn-uw@cmspan.net.. .[color=blue]
> I'm trying to create a form in Excel to sort from the form and take the data
> to another worksheet.
> I am very new to this and any help would be appreciated.
>
> I have a value in a cell that will direct what sheet I want the cells copied
> to, so I thought I could use an If statement but have been unable to right
> the code properly, ie...
>
> If "f2" = 1 Then[/color]

The string "f2" is never equal to the integer 1 :-)

I assume what you mean is something more like

If ActiveSheet.Ran ge("F2").Value = 1 Then

Or perhaps better would be

vPick = ActiveSheet.Ran ge("F2").Value

If vPick = 1 Then

That way you don't get scrambled when the active sheet gets changed.

[preben nielsen]

Re: Using &quot;IF&quo t; Statement


"StoneWare" <HejMedDig@NoSp am.Net> skrev i en meddelelse
news:4231fae0$0 $21767$edfadb0f @dread14.news.t ele.dk...[color=blue]
> Select case "f2"
> case 1[/color]

lol. Hell freezes over before that happens ;-)


--
/\ preben nielsen
\/\ prel@post.tele. dk

[preben nielsen]

Re: Using &quot;IF&quo t; Statement


"StoneWare" <HejMedDig@NoSp am.Net> skrev i en meddelelse
news:4231fae0$0 $21767$edfadb0f @dread14.news.t ele.dk...[color=blue]
> Select case "f2"
> case 1[/color]

lol. Hell freezes over before that happens ;-)


--
/\ preben nielsen
\/\ prel@post.tele. dk

[StoneWare]

Re: Using &quot;IF&quo t; Statement


"preben nielsen" <prel@post.tele .dk> skrev i en meddelelse
news:4232d916$0 $289$edfadb0f@d read11.news.tel e.dk...[color=blue]
>
> "StoneWare" <HejMedDig@NoSp am.Net> skrev i en meddelelse
> news:4231fae0$0 $21767$edfadb0f @dread14.news.t ele.dk...[color=green]
>> Select case "f2"
>> case 1[/color]
>
> lol. Hell freezes over before that happens ;-)[/color]

You do not have to LOL........I was just copying from the original post and
point out that a series "if then" in this example could be replaced by a
Select Case method......... ........no error-checking from my side...so stop
LOL............ ....




[color=blue]
>
>
> --
> /\ preben nielsen
> \/\ prel@post.tele. dk
>[/color]

[StoneWare]

Re: Using &quot;IF&quo t; Statement


"preben nielsen" <prel@post.tele .dk> skrev i en meddelelse
news:4232d916$0 $289$edfadb0f@d read11.news.tel e.dk...[color=blue]
>
> "StoneWare" <HejMedDig@NoSp am.Net> skrev i en meddelelse
> news:4231fae0$0 $21767$edfadb0f @dread14.news.t ele.dk...[color=green]
>> Select case "f2"
>> case 1[/color]
>
> lol. Hell freezes over before that happens ;-)[/color]

You do not have to LOL........I was just copying from the original post and
point out that a series "if then" in this example could be replaced by a
Select Case method......... ........no error-checking from my side...so stop
LOL............ ....




[color=blue]
>
>
> --
> /\ preben nielsen
> \/\ prel@post.tele. dk
>[/color]

[Ade]

Re: Using &quot;IF&quo t; Statement


"Scott Beavers" <sbeavers@aasur veying.com> wrote in message
news:p-KdnRsPr_34d6zfR Vn-uw@cmspan.net.. .[color=blue]
> I'm trying to create a form in Excel to sort from the form and take the[/color]
data[color=blue]
> to another worksheet.
> I am very new to this and any help would be appreciated.
>
> I have a value in a cell that will direct what sheet I want the cells[/color]
copied[color=blue]
> to, so I thought I could use an If statement but have been unable to right
> the code properly, ie...
>
> If "f2" = 1 Then
> Sheets("XX1").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 2 Then
> Sheets("XX2").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 3 Then
> Sheets("XX3").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 4 Then
> Sheets("XX4").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> Else
> If "f2" = 5 Then
> Sheets("XX5").S elect
> Range("A2").Sel ect
> ActiveSheet.Pas te
>
> End If
>
>
>[/color]

Hi,

Assuming that you have the workbook (WB) open and what you want is on the
sheet named "Sheet1"...

CellValue = WB.WorkSheets(" Sheet1").Cells( "F2").Value

OpenSheet = "XX" & Trim(CellValue)
Sheets(OpenShee t).Select
Range("A2").Sel ect
ActiveSheet.Pas te

The above might not be wholly accurate bit it's a whole lot shorter than
your example.

Ade