First result doesn't show up - drives me crazy

[floep]

Hi guys,

I've got some VBA script here and it does the job perfectly but there is one problem : the first result doesn't show up in the output ! The result of the script should look like item 1, item 2, item 3 but I always get item2, item3

Please help me ... I can't seem to find the problem !

Here is the vba code :

Dim cell As Range, rng As Range
Dim s As String
Dim c As Integer, i As Integer, a As Integer

With Worksheets("Mechanismen volgens object")
Set rng = .Range(.Cells(16, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

c = 5
i = 9
a = 13

For Each cell In rng
If cell.Offset(0, 1).Value = "x" And _
cell.Offset(0, c).Value + cell.Offset(0, i).Value + cell.Offset(0, a).Value > "0" Then
s = testing & s & cell.Value & Chr(10)
End If
Next

Worksheets("O - NW").Range("I4").Value = s

[NickHK]

I would it not meet your criteria of:
If cell.Offset(0, 1).Value = "x" Then

NickHK

"floep" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
>
> Hi guys,
>
> I've got some VBA script here and it does the job perfectly but there
> is one problem : the first result doesn't show up in the output ! The
> result of the script should look like item 1, item 2, item 3 but I
> always get item2, item3
>
> Please help me ... I can't seem to find the problem !
>
> Here is the vba code :
>
>>
>> Dim cell As Range, rng As Range
>> Dim s As String
>> Dim c As Integer, i As Integer, a As Integer
>>
>> With Worksheets("Mechanismen volgens object")
>> Set rng = .Range(.Cells(16, 1), .Cells(Rows.Count, 1).End(xlUp))
>> End With
>>
>> c = 5
>> i = 9
>> a = 13
>>
>> For Each cell In rng
>> If cell.Offset(0, 1).Value = "x" Then 'And _'
>> 'cell.Offset(0, c).Value + cell.Offset(0, i).Value +
>> cell.Offset(0, a).Value > "0" Then'
>> s = testing & s & cell.Value & Chr(10)
>> End If
>> Next
>>
>> Worksheets("O - NW").Range("I4").Value = s
>>
>
>
> --
> floep
> ------------------------------------------------------------------------
> floep's Profile:
> http://www.excelforum.com/member.php...o&userid=36853
> View this thread: http://www.excelforum.com/showthread...hreadid=565970
>

[floep]

I solved the problem fo the first result. My range was to narrow defined. I changed it and now I get an error on countc.

With Worksheets("Mechanismen volgens object")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

For Each cell In rng
countc = cell.Offset(0, 5).Value
If cell.Offset(0, 2).Value = "x" Then
s = s & cell.Value & Chr(10)
End If
Next

[Tom Ogilvy]

Is Item 1 in row 16 or greater?

--
Regards,
Tom Ogilvy


"floep" wrote:

>
> Hi guys,
>
> I've got some VBA script here and it does the job perfectly but there
> is one problem : the first result doesn't show up in the output ! The
> result of the script should look like item 1, item 2, item 3 but I
> always get item2, item3
>
> Please help me ... I can't seem to find the problem !
>
> Here is the vba code :
>
> >
> > Dim cell As Range, rng As Range
> > Dim s As String
> > Dim c As Integer, i As Integer, a As Integer
> >
> > With Worksheets("Mechanismen volgens object")
> > Set rng = .Range(.Cells(16, 1), .Cells(Rows.Count, 1).End(xlUp))
> > End With
> >
> > c = 5
> > i = 9
> > a = 13
> >
> > For Each cell In rng
> > If cell.Offset(0, 1).Value = "x" Then 'And _'
> > 'cell.Offset(0, c).Value + cell.Offset(0, i).Value +
> > cell.Offset(0, a).Value > "0" Then'
> > s = testing & s & cell.Value & Chr(10)
> > End If
> > Next
> >
> > Worksheets("O - NW").Range("I4").Value = s
> >
>
>
> --
> floep
> ------------------------------------------------------------------------
> floep's Profile: http://www.excelforum.com/member.php...o&userid=36853
> View this thread: http://www.excelforum.com/showthread...hreadid=565970
>
>

[floep]

Hi Tom,

The items are in column 1, the types in columns 2 till 5 and the properties in columns 6 till 16. I tried to crack the problem with the code below but now it gives me an error on 'cell.Offset(0, 5).Value + cell.Offset(0, 9).Value + cell.Offset(0, 13).Value' when I try to run the code. If I don't do the extra filtering (the filtering after the and _) the code runs fine. It's like the offsets are out of range. There is also always an extra empty line in the output. Thanks for your help ...

Dim cell As Range, rng As Range
Dim s As String

With Worksheets("Mechanismen volgens object")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

For Each cell In rng
If cell.Offset(0, 1).Value = "x" and _
cell.Offset(0, 5).Value + cell.Offset(0, 9).Value + cell.Offset(0, 13).Value > "0" Then
s = s & cell.Value & Chr(10)
End If
Next

[floep]

Hi Tom,

Item 1 is in row 2 instead of 16 ... that was my mistake :-)

Floep