PDA

View Full Version : code help (newbie)



jzamilpa3
06-26-2008, 08:49 AM
I have a code where it should look at a columnW, starting row9 and look for a number if there is a number found there should be another number outputted to Column R of the same row where the number was found.

I was told to try this but im not having any luck figuring it out and getting it to work.

let x=1 'Or start with the new row # only
let Y=the column where the number is
let Z=the column where you want your calculation to go
do while true
if cells(x,y).value=empty then exit do
if cells(x,Y).value=36 or cells(x,y).value=44 or cells(x,y).value=45 or
cells(x,y).value=46 then
let cells(x,z).value=1
elseif cells(x,y).value=37 then '(and the other values for 3)
let cells(x,z).value=3
etc.etc.etc....
end if
x=x+1
Loop

this is the set of numbers that should be looked up between any wording in the cell. the ones on the right have to equal the ones on the left.
1= 36,44,45,46
3= 37,38,39,54,55
5= 40-43
6= 47,48,49, 146-159, 201-210
7= 23, 83,84, 211
10= 212,214, 227
11= 57-82, 87-136, 163,167, 199, 213
12= 31-34
13= 21,22, 24-30, 160-197
14= 85,86
15= 139-141
16= 1,2,13,14,17,18,53
17= 3,4,15,16,20
19= 50,51,52
20= 19
22= 142-145

lenze
06-26-2008, 08:52 AM
So if your cell had 17 in it, R would be 3,4,15,16,20 ?

lenze

jzamilpa3
06-26-2008, 09:02 AM
So if your cell had 17 in it, R would be 3,4,15,16,20 ?

lenze

no u hav it backwards if in columnW it has either of these 3,4,15,16,20 it should = 17 in column R

lenze
06-26-2008, 10:54 AM
Assuming Column "Q", starting at Row 9. Change as needed.


Option Explicit
Sub EnterNums()
Dim cl As Range
Dim myVal As Variant
For Each cl In Range("$Q$9:$Q" & Range("$Q$65536").End(xlUp).Row)
Select Case cl
Case Is = 35, 44, 45, 46: myVal = 1
Case Is = 37, 38, 39, 54, 55: myVal = 3
Case Is = 40, 41, 42, 43: myVal = 5
Case Is = 47, 48, 49, 146 To 159, 201 To 210: myVal = 6
'etc
Case Else:
End Select
Cells(cl.Row, "R") = myVal
Next cl
End Sub


lenze

jzamilpa3
06-26-2008, 10:58 AM
thnx ill try it and let you know if it works.
much appreciated

jzamilpa3
06-26-2008, 11:29 AM
here's what i have and im working with it it didnt work for some reason.
as u can see its workin off a command button

Private Sub CommandButton1_Click()
Dim j, a, b, g As Integer
Dim strBlah As String

Application.Calculation = xlCalculationManual
Sheet2.Range("A9:AB20000").Select
Selection.WrapText = True
g = 9 'starting row

For a = 2 To 20000
If Sheet3.Cells(a, 14) = Sheet2.Cells(2, 2) Then
'If Sheet1.Cells(a, 34).Value = Sheet2.cmbVTYPE.Text Then
Sheet2.Cells(g, 1) = "CWS"
Sheet2.Cells(g, 2) = "CWS-BG-" & Sheet3.Cells(a, 3)
Sheet2.Cells(g, 3) = "TRUE"
Sheet2.Cells(g, 4) = "FALSE"
Sheet2.Cells(g, 5) = "FALSE"
Sheet2.Cells(g, 6) = "FALSE"
Sheet2.Cells(g, 7) = "FALSE"
Sheet2.Cells(g, 8) = Sheet3.Cells(a, 3)
Sheet2.Cells(g, 11) = Sheet3.Cells(a, 18)
Sheet2.Cells(g, 12) = Sheet3.Cells(a, 1)
'Sheet2.Cells(g, 16) = Sheet3.Cells(a, 11)
strBlah = Sheet3.Cells(a, 6)
Sheet2.Cells(g, 24) = strBlah
g = g + 1
End If
Next a

For Each cl In Range("$Q$9:$Q$" & Range("$Q$6553").End(xlUp).Row)
Select Case cl
Case Is = 35, 44, 45, 46: myVal = 1
Case Is = 37, 38, 39, 54, 55: myVal = 3
Case Is = 40, 41, 42, 43: myVal = 5
Case Is = 47, 48, 49, 146 To 159, 201 To 210: myVal = 6
'etc
Case Else:
End Select
Cells(cl.Row, "Y") = myVal
Next cl
End Sub

Application.Calculation = xlCalculationAutomatic
End Sub

lenze
06-26-2008, 11:38 AM
I can't follow what you are trying to do with the 1st part of your code. You declared j,a,b,g. What happened to j & b?
Also note your typo here


For Each cl In Range("$Q$9:$Q" & Range("$Q$6553").End(xlUp).Row)

Should be
For Each cl In Range("$Q$9:$Q" & Range("$Q$65536").End(xlUp).Row)


lenze

jzamilpa3
06-26-2008, 11:42 AM
I can't follow what you are trying to do with the 1st part of your code. You declared j,a,b,g. What happened to j & b?
Also note your typo here


For Each cl In Range("$Q$9:$Q" & Range("$Q$6553").End(xlUp).Row)

Should be
For Each cl In Range("$Q$9:$Q" & Range("$Q$65536").End(xlUp).Row)


lenze

oh j and b are not being used. so i guess i could delete that from there.

jzamilpa3
06-26-2008, 12:03 PM
Cells(cl.Row, "R") = myVal

this should also start at row 9

lenze
06-26-2008, 01:11 PM
Cells(cl.Row, "R") = myVal

this should also start at row 9
It does!! Since the first cl is Q9, the 1st cl.Row = 9

lenze

jzamilpa3
06-26-2008, 08:32 PM
It does!! Since the first cl is Q9, the 1st cl.Row = 9

lenze

ok, but it clears the full column. and i still cant get it to work. i think it checks it but it doesnt output anything

jzamilpa3
06-27-2008, 05:52 AM
bump help needed

lenze
06-27-2008, 11:00 AM
Until you explain what you are trying to do (and why it is needed) with the first part of your code, I can't be of further assistance. The code I posted does exactly what you asked for in your OP. Now you are combining it with other code which is probably causing problems. From what you've posted, I can't tell what is going on and where you want things placed.
lenze