If statement and Qualifying help-- please

[YellowLabPro]

I could use a little more assistance on three items, small in nature I believe.

1) The if statment here is returning a False value in the Target sheet. I need it to return "GRL"

If Wss.Cells(i, "F").Value = "SURFW" Then
Wst.Cells(LRowt, "P").Value = "GRL" = Wss.Cells(i, "F").Value 'Girls
End If

2) Second part of the first item- rather than hard-code the finds to SURFW or SKATEW, I would like to find all the terms in "F" that end in W. If I use an "*W" will this work?

3) When I run my code while it is on the target sheet, rather than the source sheet, I receive the out of range error msg.
But I thought I had qualified all my statments that it would not matter when I ran the code what sheet I was on, or was active. I am posting the entire code-- this is an important fundamental issue I need to understand and I have run into it several times. My only way to solve in the past has been to go to the source worksheet and run it, not good-- :doh:



Sub Receive_From_PO()
Dim Wbs As Workbook
Dim Wss As Worksheet, Wst As Worksheet
Dim i As Long
Dim LRowt As Long
Dim j As Variant
Dim WbsName As String, WbtName As String, WstName As String
Dim vArray() As Variant
'WbsName = "Rusty AO"
'WbsName = Application.Inputbox("Enter PO File Name")
WbsName = Application.ActiveWorkbook.Name
WbtName = "TGSItemRecordCreatorMaster.xls"
WstName = "Record Creator"
j = Application.Inputbox("Enter Column Letter for which Shipment# to Receive")
If Not IsNumeric(j) Then j = Columns(j).Column()
vArray = Array("Surf", "Skate", "Snow", "Wake")
Set Wss = Workbooks(WbsName).Worksheets("FFReceived")
Set Wst = Workbooks(WbtName).Worksheets(WstName)
For i = 2 To Wss.Cells(Rows.Count, "I").End(xlUp).Row
LRowt = Wst.Cells(Rows.Count, "I").End(xlUp).Row + 1
If Val(Cells(i, j)) > 0 Then
With Wst
.Cells(LRowt, "AC").Value = Wss.Cells(i, j).Value ' Qty = col. AC/29
.Cells(LRowt, "H").Value = Wss.Cells(i, "A").Value ' Company
.Cells(LRowt, "I").Value = Wss.Cells(i, "C").Value ' Item Name
.Cells(LRowt, "M").Value = Wss.Cells(i, "D").Value ' Color
'.Cells(LRowt, "J").Value = Wss.Cells(i, "E").Value ' Item
.Cells(LRowt, "J").Value = Wss.Cells(i, "E").Offset(0, IIf(Not IsError(Application.Match(Wss.Cells(i, "E").Value, vArray, 0)), 1, 0)).Value ' Item
.Cells(LRowt, "Z").Value = Wss.Cells(i, "G").Value ' Cost
.Cells(LRowt, "N").Value = Wss.Cells(i, "H").Value ' Size
End With
End If
If Wss.Cells(i, "F").Value = "SURFW" Then
Wst.Cells(LRowt, "P").Value = "GRL" = Wss.Cells(i, "F").Value 'Girls
End If
Next i
End Sub

Thanks,

Doug

[mvidas]

I could use a little more assistance on three items, small in nature I believe.

1) The if statment here is returning a False value in the Target sheet. I need it to return "GRL"

If Wss.Cells(i, "F").Value = "SURFW" Then
Wst.Cells(LRowt, "P").Value = "GRL" = Wss.Cells(i, "F").Value 'Girls
End If
What this is really doing is putting a true/false value into Wst.Cells(LRowt, "P") depending if "GRL" = Wss.Cells(i, "F").Value
I'm guessing you'll just want to remove the ' = Wss.Cells(i, "F").Value'


2) Second part of the first item- rather than hard-code the finds to SURFW or SKATEW, I would like to find all the terms in "F" that end in W. If I use an "*W" will this work? you could do if ucase(wst.cells(lrowt,"p").value) like "*W" thenor even just if right(ucase(wst.cells(lrowt,"p").value,1)="W" then

3) When I run my code while it is on the target sheet, rather than the source sheet, I receive the out of range error msg.
But I thought I had qualified all my statments that it would not matter when I ran the code what sheet I was on, or was active. I am posting the entire code-- this is an important fundamental issue I need to understand and I have run into it several times. My only way to solve in the past has been to go to the source worksheet and run it, not good-- :doh:

Thanks,

DougWhat line are you getting the 'subscript out of range' error? I'm guessing this line:If Not IsNumeric(j) Then j = Columns(j).Column()All of your "Rows.Count" are unqualified but that really shouldn't do it (since all your worksheets should have the same row count). I assume it is the line i mention, as if j is "" or "false" or "hi doug" then Columns("hi doug") wouldn't work

EDIT: the following line also is unqualified:If Val(Cells(i, j)) > 0 Then

[YellowLabPro]

Matt,
thanks.
Number 1 is solved.
Number 2 needs to be reversed in the way you wrote it back, but that is ok, because there is a flaw in my logic. I need to add an Array statement and an And statement.
I need your help in writing this-
Wss ) In col. E, = IF ISNOT Surf, Skate, Snow, Wake, FTWR, and Col. F last character is a "W" Then
Put the value "GRL" in "P" on Wst

I am playing around w/ it now.... but would like your help if you have the time,

Thanks very much,

Doug

[YellowLabPro]

Matt,
The logic is still flawed....
Let me think on this for a minute and post back. Sorry....

[YellowLabPro]

Matt,
We need two conditions met:
If (i,e) isnot surf,skate,snow,wake,ftwr and i,f ends "W" and is not "Snow" then place Grl in P
If (i,e) is surf, skate,snow,wake, ftwr and i,f ends "w" then place Grl in P

What is going on is clothing items are departmentalized by the item, Tee, Pant, Short and then categorized by sport, Surf,Skate, Snow, Wake, FTWR (FTWR is an anamoly). If it is a female item, then a W is attached to the end of the category, Surfw, Skatew, Snoww, Wakew, Ftwrw.
If an item is sport related, it gets departmentalized by the sport, Surf,Skate, Snow, Wake, and then categorized by the gender, which gets a "W" added onto the end.
I took time to write this incase you see a different option for the logic.

thanks,

Doug

[mvidas]

I need your help in writing this-
Wss ) In col. E, = IF ISNOT Surf, Skate, Snow, Wake, FTWR, and Col. F last character is a "W" Then
Put the value "GRL" in "P" on Wst
so for the rows where (column E is not surf,skate,snow,wake,ftwr) and (column F last character is a w), put GRL in column p? what do you want in p otherwise?

Do you want it like (pseudocode, obviously):If "column E is not surf,skate,snow,wake,ftwr" Then
If "column F last character is a w" Then
'put GRL in p
Else
'do something if col E isnt one of those but last char isnt w
End If
Else
'do something if col E IS one of those
End IfOr do you want it like:If "column E is not surf,skate,snow,wake,ftwr" And "column F last character is a w" Then
'put GRL in p
Else
'do something if col E is one of those or last char isnt W
End If

[mvidas]

If (i,e) isnot surf,skate,snow,wake,ftwr and i,f ends "W" and is not "Snow" then place Grl in P
If (i,e) is surf, skate,snow,wake, ftwr and i,f ends "w" then place Grl in P
Based on that alone, it looks like you could just do "if last character of (i,f) is W" and "(i,f) isnt snow" .. or am I reading this wrong?

[YellowLabPro]

I think your idea:


Based on that alone, it looks like you could just do "if last character of (i,f) is W" and "(i,f) isnt snow" .. or am I reading this wrong?
is correct.

What goes in "P" if the last character is not a "w" and is not snow is nothing.

[mvidas]

Still need help with it then?
If Right(UCase(Wss.Cells(i, "F").Value), 1) = "W" And _
UCase(Wss.Cells(i, "F").Value) <> "SNOW" Then
Wst.Cells(LRowt, "P").Value = "GRL"
End If

[YellowLabPro]

Matt,
Perfect-- thanks!.

I have been working on the last part while you were hammering the other portion out for me--
Here is what I have:

The error I run into when running it while the Source sheet is not active is out of range two-fold:
Set Wss = Workbooks(WbsName).Worksheets("FFReceived")

Issue 1 is I just realized: Out of Range: is because the Activeworkbook is something else other than WbsName = Application.ActiveWorkbook.Name (I am in a different workbook and it does not have the target worksheet.) Doh...

So I can get around this by going back to:
WbsName = Application.Inputbox("Enter PO File Name")

I still have two more things to resolve though-
1) Resolving the above issue by using the inputbox, if I am on the target sheet Wst, or some other worksheet other than the source, when I run the code, all the copying of the values does nothing. This is where I was explaining earlier that I thought I had things qualified to perform the tasks irregardless of what workbooks/sheets were active. This is not a huge problem because eventually these will be assigned to buttons. But what I am more interested in is seeing why this fails. Make sense?


Set Wss = Workbooks(WbsName).Worksheets("FFReceived")
Set Wst = Workbooks(WbtName).Worksheets(WstName)
For i = 2 To Wss.Cells(Rows.Count, "I").End(xlUp).Row
LRowt = Wst.Cells(Rows.Count, "I").End(xlUp).Row + 1
If Val(Cells(i, j)) > 0 Then
With Wst
.Cells(LRowt, "AC").Value = Wss.Cells(i, j).Value ' Qty = col. AC/29
.Cells(LRowt, "H").Value = Wss.Cells(i, "A").Value ' Company
.Cells(LRowt, "I").Value = Wss.Cells(i, "C").Value ' Item Name
.Cells(LRowt, "M").Value = Wss.Cells(i, "D").Value ' Color
'.Cells(LRowt, "J").Value = Wss.Cells(i, "E").Value ' Item
.Cells(LRowt, "J").Value = Wss.Cells(i, "E").Offset(0, IIf(Not IsError(Application.Match(Wss.Cells(i, "E").Value, vArray, 0)), 1, 0)).Value ' Item
.Cells(LRowt, "Z").Value = Wss.Cells(i, "G").Value ' Cost
.Cells(LRowt, "N").Value = Wss.Cells(i, "H").Value ' Size
End With
End If


2) The line: If Not IsNumeric(j) Then j = Columns(j).Column() is ok as long as as I enter a letter, if I enter a value it fails now.
Without that line and I enter a letter it fails and a numerical value runs ok. I would like to have the ability to put either of them in there. MDmackillop showed me once how to do this, but I am not able to reproduce.


Thanks once more....

[mvidas]

Re: your WbsName stuff, what about something like: WbsName = ActiveWorkbook.Name
WbtName = "TGSItemRecordCreatorMaster.xls"
WstName = "Record Creator"
Set Wst = Workbooks(WbtName).Worksheets(WstName)
Do
On Error Resume Next
Set Wss = Workbooks(WbsName).Worksheets("FFReceived")
On Error GoTo 0
If Wss Is Nothing Then WbsName = InputBox("Enter PO File Name")
Loop Until Not Wss Is NothingAnother option would be to loop through all open workbooks until you find one with a "FFReceived" sheet


Re: your #1, you need to qualify the 'Cells' in "If Val(Cells(...". I added it onto my previous post as an EDIT about 30 seconds after posting, however you may have missed it.

Re: your #2 ... i think you lost me. at face value that code is fine, you'd only error out if you didnt have a workbook open. You may want to do something like If Not IsNumeric(j) Then j = Columns(j).Column Else j = CLng(j)As j is declared a variant and inputbox returns a string (even though you're using the application.inputbox, which IS different than vba.inputbox or just plain inputbox, you dont have a Type listed so it is returning text anyways).