Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > search and/or loop dilema
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default search and/or loop dilema
After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default search and/or loop dilema

Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 < 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub


"nancy/karen" wrote:

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default search and/or loop dilema
Joel -

thank you for your help. I have entered and verified my entry with what
you sent.
I was not able to place the (:) semi-colon between the word "destination"
and the (=) equal sign. The program did run without it. But, as written, it
did not preform according to my expectations. What it did do was highlight
row 14 as if to copy - that's it.

suggestions?

--
N/K


"Joel" wrote:


Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 < 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub


"nancy/karen" wrote:

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default search and/or loop dilema
You must of lost the line continuation character after the copy on the
previous line. Look at my posted code.

"nancy/karen" wrote:

Joel -

thank you for your help. I have entered and verified my entry with what
you sent.
I was not able to place the (:) semi-colon between the word "destination"
and the (=) equal sign. The program did run without it. But, as written, it
did not preform according to my expectations. What it did do was highlight
row 14 as if to copy - that's it.

suggestions?

--
N/K


"Joel" wrote:


Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 < 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub


"nancy/karen" wrote:

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default search and/or loop dilema
I deleted my entry, copied and pasted yours. I received run time error
"1004"

In debugging mode, it highlights the .copy _ line.
Am I missing an "add-in" or perhpas I have an older version of Excel?
--
N/K


"Joel" wrote:

You must of lost the line continuation character after the copy on the
previous line. Look at my posted code.

"nancy/karen" wrote:

Joel -

thank you for your help. I have entered and verified my entry with what
you sent.
I was not able to place the (:) semi-colon between the word "destination"
and the (=) equal sign. The program did run without it. But, as written, it
did not preform according to my expectations. What it did do was highlight
row 14 as if to copy - that's it.

suggestions?

--
N/K


"Joel" wrote:


Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 < 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub


"nancy/karen" wrote:

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default search and/or loop dilema
----I tested the code and it is working. Here are some reasons you may have
a problem

1) You have more than one workbook open. Add Thisworkbook to specfy the
workbook with the macro

from
With Sheets("daily production")
to
With Thisworkbook.Sheets("daily production")

2) Make surre the code is in the correct VBA sheet. Since the code works
off a button it should be in the VBA sheet "daily production"


3) Make sure the sheet name "daily production" matches the sheet name on the
worksheet tab. Make surre no spaces are at the beginning and end on the tab
and don't put the double quotes in the tab

4) You have merged cells that you are copying or locked cells. Try deleting
columns AN: AX and try again.

This code is very simple and shouldn't fail.


"nancy/karen" wrote:

I deleted my entry, copied and pasted yours. I received run time error
"1004"

In debugging mode, it highlights the .copy _ line.
Am I missing an "add-in" or perhpas I have an older version of Excel?
--
N/K


"Joel" wrote:

You must of lost the line continuation character after the copy on the
previous line. Look at my posted code.

"nancy/karen" wrote:

Joel -

thank you for your help. I have entered and verified my entry with what
you sent.
I was not able to place the (:) semi-colon between the word "destination"
and the (=) equal sign. The program did run without it. But, as written, it
did not preform according to my expectations. What it did do was highlight
row 14 as if to copy - that's it.

suggestions?

--
N/K


"Joel" wrote:


Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 < 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub


"nancy/karen" wrote:

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 < 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 < "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Do Loop or use End iF for search string RGreen Excel Discussion (Misc queries) 4 September 1st 09 12:59 AM
IF Dilema Shu of AZ Excel Discussion (Misc queries) 10 December 24th 06 06:15 PM
Quick Search Loop Sami82[_20_] Excel Programming 1 November 4th 05 01:15 PM
how do i loop in a access search hans[_2_] Excel Programming 3 July 28th 03 09:35 AM
search & copy with loop Don Guillett[_4_] Excel Programming 0 July 24th 03 02:55 PM


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement