Edit Specific Range if Cell Value is True

[temerson]

Hello,

I have the code below:

Sub editrange

Dim nLastRow, i As Integer

Set wbCurrent = ActiveWorkbook


Set WS = wbCurrent.Worksheets("Sheet1")
nLastRow = WS.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For i = nLastRow To 13 Step -1
If InStr(1, WS.Cells(i, 7).Value, "Blue", vbTextCompare) > 0 Then
Else
this is where i want to put in a value in a specific range (edit all cells in row(s) that has a "b" in row 2)
End If

Next i

End Sub

 

[hernantorres23]

Hi, I'm following your code to try answer your cuestions

Please use this example or put your own, for help us to understand your questions

Also, answer the question, that you see like comments inside the code

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Key1	Number 1	Meaning 1	Key2	Number 2	Meaning 2	Key3	Number 3	Meaning 3	Key4
3	Avaya	54366		Tabel	443948		Green			bebe
4	Avaya	54476		Tabel	443958		DPA	Max@gmail		vzla
5	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat
6	Tabel	444948		Avaya	54466		Red			betacaroteno
7	Avaya	54466		Tabel	443948		DPA	Th@gmail		tambien
8	Chat	121312		Avaya	54466		Tabel	443948		DPA
9	Avaya	54366		Tabel	443948		Yellow			bebe
10	Avaya	54476		Tabel	443958		DPA	Max@gmail		vzla
11	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat
12	Tabel	444948		Avaya	54466		Blue			betacaroteno
13	Avaya	54466		Tabel	443948		DPA	Th@gmail		tambien
14	Chat	121312		Avaya	54466		Tabel	443948		DPA
15	Avaya	54366		Tabel	443948		Blue			bebe
16	Avaya	54476		Tabel	443958		DPA	Max@gmail		vzla
17	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat
18	Tabel	444948		Avaya	54466		Blue			betacaroteno
19	Avaya	54466		Tabel	443948		DPA	Th@gmail		tambien
20	Chat	121312		Avaya	54466		Tabel	443948		DPA
21	Avaya	54366		Tabel	443948		Blue			bebe
22	Avaya	54476		Tabel	443958		DPA	Max@gmail		vzla
23	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat
24	Tabel	444948		Avaya	54466		Blue			betacaroteno
25	Avaya	54466		Tabel	443948		DPA	Th@gmail		tambien
26	Chat	121312		Avaya	54466		Tabel	443948		DPA
temerson

Option Explicit
'Refers to temerson sheet
'With this code, you are checking the last 13 cells in column G
'Tell me something, what condition I will take, to put the value in every cell in row 2?
'I ask, because you need this inside the Else statement.
'I'm sorry, It just doesn't make sense, to evaluate this inside Else clause,
'as it would repeat in every cycle

Sub editrange()

Dim nLastRow, i As Integer
Dim wbCurrent As Workbook
Dim ws As Worksheet

Set wbCurrent = ActiveWorkbook
Set ws = wbCurrent.Worksheets("temerson")
nLastRow = ws.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For i = nLastRow To 13 Step -1
If InStr(1, ws.Cells(i, 7).Value, "Blue", 1) > 0 Then
MsgBox "Found Blue in " & Cells(i, 7).Address, vbInformation, "Mikel ERP by htorres"
Else
'this is where i want to put in a value in a specific range (edit all cells in row(s) that has a "b" in row 2)
End If

Next i
End Sub

 

[temerson]

Hello,

I think I explained my goal incorrectly. I have attached an example.

'the code below searches "Blue" column G all the way up to row 13. I don't want rows 1-12 to be searched.
nLastRow = ws.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For i = nLastRow To 13 Step -1
If InStr(1, ws.Cells(i, 7).Value, "Blue", 1) > 0 Then

'I should have put the bold sentence below above the Else. Basically, if "Blue" is found, I would like to edit cells in that row if also B is found in row 2 starting with column V

Else
'this is where i want to put in a value in a specific range (edit all cells in row(s) that has a "b" in row 2)
End If

If it does not find Blue, then nothing happens.

 

[hernantorres23]

Thanks a lot, but please try prepare a data example using add-in specifically for this and it can be found here XL2BB
If necessary, pay attention to this post XL2BB 2 Square