Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 2nd 08, 09:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 142
Default Update sheets & cycle

I'm using the following code to update a sheet2 based on values in
first column of sheet1.
I want to modify to cycle across two more sheets against column 2 &
column 3 of sheet1

sheet1----column1-------for------sheet2

sheet1----column2-------for------sheet3
sheet1----column3-------for------sheet4

-------------------------------

Dim vArr As Variant
Dim rCell As Range
Dim rDelete As Range
Dim nLow As Long
Dim nHigh As Long
Dim i As Long
Dim sTest As String

Sub Update_List()

With Sheets("Sheet1")
vArr = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp)).Value
End With
nLow = LBound(vArr, 1)
nHigh = UBound(vArr, 1)
With Sheets("Sheet2")
For Each rCell In .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
sTest = rCell.Text
For i = nLow To nHigh
If sTest = vArr(i, 1) Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next i
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End With
End Sub

  #2   Report Post  
Old March 2nd 08, 10:23 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 703
Default Update sheets & cycle

Hi

Try this modification. If needed you can add more sheets to the
"shArr" array.

Dim vArr As Variant
Dim rCell As Range
Dim rDelete As Range
Dim nLow As Long
Dim nHigh As Long
Dim i As Long
Dim sTest As String
Dim shArr As Variant

Sub Update_List()
shArr = Array("Sheet2", "Sheet3", "Sheet4")
For sh = 0 To UBound(shArr)
With Sheets("Sheet1")
vArr = .Range(.Cells(1, 1 + sh), _
.Cells(.Rows.Count, 1 + sh).End(xlUp)).Value
End With
nLow = LBound(vArr, 1)
nHigh = UBound(vArr, 1)
With Sheets(shArr(sh))
For Each rCell In .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp))

sTest = rCell.Text
For i = nLow To nHigh
If sTest = vArr(i, 1) Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next i
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End With
Next
End Sub

Regards,

Per

On 2 Mar., 21:37, Sinner wrote:
I'm using the following code to update a sheet2 based on values in
first column of sheet1.
I want to modify to cycle across two more sheets against column 2 &
column 3 of sheet1

sheet1----column1-------for------sheet2

sheet1----column2-------for------sheet3
sheet1----column3-------for------sheet4

-------------------------------

Dim vArr As Variant
* * Dim rCell As Range
* * Dim rDelete As Range
* * Dim nLow As Long
* * Dim nHigh As Long
* * Dim i As Long
* * Dim sTest As String

Sub Update_List()

* * With Sheets("Sheet1")
* * * * vArr = .Range(.Cells(1, 1), _
* * * * * * * * .Cells(.Rows.Count, 1).End(xlUp)).Value
* * End With
* * nLow = LBound(vArr, 1)
* * nHigh = UBound(vArr, 1)
* * With Sheets("Sheet2")
* * * * For Each rCell In .Range(.Cells(1, 1), _
* * * * * * * * .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * sTest = rCell.Text
* * * * * * For i = nLow To nHigh
* * * * * * * * If sTest = vArr(i, 1) Then
* * * * * * * * * * If rDelete Is Nothing Then
* * * * * * * * * * * * Set rDelete = rCell
* * * * * * * * * * Else
* * * * * * * * * * * * Set rDelete = Union(rDelete, rCell)
* * * * * * * * * * End If
* * * * * * * * End If
* * * * * * Next i
* * * * Next rCell
* * * * If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
* * End With
End Sub


  #3   Report Post  
Old March 2nd 08, 11:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 142
Default Update sheets & cycle

On Mar 3, 2:23*am, Per Jessen wrote:
Hi

Try this modification. If needed you can add more sheets to the
"shArr" array.

Dim vArr As Variant
* * Dim rCell As Range
* * Dim rDelete As Range
* * Dim nLow As Long
* * Dim nHigh As Long
* * Dim i As Long
* * Dim sTest As String
* * Dim shArr As Variant

Sub Update_List()
* * shArr = Array("Sheet2", "Sheet3", "Sheet4")
* * For sh = 0 To UBound(shArr)
* * With Sheets("Sheet1")
* * * * vArr = .Range(.Cells(1, 1 + sh), _
* * * * * * * * .Cells(.Rows.Count, 1 + sh).End(xlUp)).Value
* * End With
* * nLow = LBound(vArr, 1)
* * nHigh = UBound(vArr, 1)
* * With Sheets(shArr(sh))
* * * * For Each rCell In .Range(.Cells(1, 1), _
* * * * * * * * .Cells(.Rows.Count, 1).End(xlUp))

* * * * * * sTest = rCell.Text
* * * * * * For i = nLow To nHigh
* * * * * * * * If sTest = vArr(i, 1) Then
* * * * * * * * * * If rDelete Is Nothing Then
* * * * * * * * * * * * Set rDelete = rCell
* * * * * * * * * * Else
* * * * * * * * * * * * Set rDelete = Union(rDelete, rCell)
* * * * * * * * * * End If
* * * * * * * * End If
* * * * * * Next i
* * * * Next rCell
* * * * If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
* * End With
* * Next
End Sub

Regards,

Per

On 2 Mar., 21:37, Sinner wrote:



I'm using the following code to update a sheet2 based on values in
first column of sheet1.
I want to modify to cycle across two more sheets against column 2 &
column 3 of sheet1


sheet1----column1-------for------sheet2


sheet1----column2-------for------sheet3
sheet1----column3-------for------sheet4


-------------------------------


Dim vArr As Variant
* * Dim rCell As Range
* * Dim rDelete As Range
* * Dim nLow As Long
* * Dim nHigh As Long
* * Dim i As Long
* * Dim sTest As String


Sub Update_List()


* * With Sheets("Sheet1")
* * * * vArr = .Range(.Cells(1, 1), _
* * * * * * * * .Cells(.Rows.Count, 1).End(xlUp)).Value
* * End With
* * nLow = LBound(vArr, 1)
* * nHigh = UBound(vArr, 1)
* * With Sheets("Sheet2")
* * * * For Each rCell In .Range(.Cells(1, 1), _
* * * * * * * * .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * sTest = rCell.Text
* * * * * * For i = nLow To nHigh
* * * * * * * * If sTest = vArr(i, 1) Then
* * * * * * * * * * If rDelete Is Nothing Then
* * * * * * * * * * * * Set rDelete = rCell
* * * * * * * * * * Else
* * * * * * * * * * * * Set rDelete = Union(rDelete, rCell)
* * * * * * * * * * End If
* * * * * * * * End If
* * * * * * Next i
* * * * Next rCell
* * * * If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
* * End With
End Sub- Hide quoted text -


- Show quoted text -


-----------------------------
Just need to add the following:

Read as:

sheet1----column1-------data to update------sheet2-----data in------
column3

sheet1----column2-------data to update------sheet3-----data in------
column3
sheet1----column3-------data to update------sheet4 ----data in-------
column3
--------------------------

Thx.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
How to update data from multiple sheets to one specific sheets Khawajaanwar Excel Discussion (Misc queries) 4 January 15th 10 08:31 AM
how do i set up attendance sheets for 2 week repeting work cycle bill Excel Discussion (Misc queries) 0 January 5th 09 03:58 PM
crtl + page down or up does not cycle through sheets in excel 2007 John G.[_2_] Excel Discussion (Misc queries) 4 May 8th 08 05:21 PM
Cycle through multiple sheets karimhemani Excel Programming 2 March 19th 07 07:18 AM
How do I keep result from 1 iteration cycle to use in next cycle? sgl8akm Excel Discussion (Misc queries) 0 July 27th 06 08:28 PM


All times are GMT +1. The time now is 04:18 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017