-
Sep 10th, 2009, 03:00 PM
#1
Thread Starter
Member
[RESOLVED] Change cell value via another cell value
I have this code that I use to delete rows based off of a variable in the columns. It loops through and finds every row with that value and deletes the row.
I'm trying to do everything the same except instead of deleting a row, I want to change the value of a different cell.
Code:
Sub DeleteRows()
Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim mCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria
On Error Resume Next
'Determine the table range
With Sheets("HRC").Cells.Select
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
vCriteria = Application.InputBox(Prompt:="Type in the criteria that macthing rows should be deleted. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)
If vCriteria = "False" Then Exit Sub
lCol = 3
'Set rCol to the column where criteria should be found
Set rCol = rTable.Columns(lCol)
'Set rCell to the first data cell in rCol
Set rCell = rCol.Cells(2, 1)
'Store current Calculation then switch to manual.
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
'Loop and delete as many times as vCriteria exists in rCol
For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)
Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(-1, 0)
[B]rCell.Offset(1, 0).entirerow.delete[/B]
'THIS IS WHERE IT DELETES, NEED TO SELECT ANOTHER CELL AND 'CHANGE THE VALUE OF THAT CELL
Next lCol
'Put back calculation to how it was.
Application.Calculation = xlCalc
On Error GoTo 0
End Sub
-
Sep 10th, 2009, 03:37 PM
#2
Re: Change cell value via another cell value
rCell.Offset(0, 1) = "your value"
will change the value of next cell in the same row...
rCell.Offset(0, 5) = "your value"
will change the value of the 5th cell on the right in the same row...
Also what if Set rCell = rCol.Find(What.... fails?
See this thread on how to use .find
http://www.vbforums.com/showpost.php...68&postcount=2
Hope this helps...
Last edited by Siddharth Rout; Sep 10th, 2009 at 03:43 PM.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 10th, 2009, 03:44 PM
#3
Thread Starter
Member
Re: Change cell value via another cell value
That works but for some reason (I had gotten to this point before) it ends the For Next Loop.
There will normally be multiple values on a sheet, so it has to loop through the column looking for a specific value then change the cell to the right if it matches the value.
-
Sep 10th, 2009, 03:49 PM
#4
Re: Change cell value via another cell value
I have suggested a better way of doing it... see the previous post...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 10th, 2009, 04:12 PM
#5
Thread Starter
Member
Re: Change cell value via another cell value
yeah I still can't get it to loop right...IDK what I'm doing wrong?
-
Sep 10th, 2009, 04:19 PM
#6
Re: Change cell value via another cell value
ok let me see what can i do for you... give me few moments...
edit: Ok see this example on how to use .find and continue down the column without using for next loop...
http://www.vbforums.com/showpost.php...31&postcount=8
Last edited by Siddharth Rout; Sep 10th, 2009 at 04:39 PM.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|