lookup and replace in vba

[B-Man]

im having trouble sorting out this vba formula I can handle an if formula in the sheet but really struggling in vba

I have this list on sheet1
sheet1 column P is tickboxes (True/False for that column)
if there's nothing in sheet1 column O then skip it
if there's something in sheet1 column O I want to check if sheet1 column P is "true" and lookup the word from sheet1 column N in sheet2 column D put value "sold" in sheet2 column F (offset 2?) if false do nothing
then replace the word from sheet1 column N in sheet2 range D:D with sheet1 column O

NFO New Jot.xlsm
	N	O	P
29	List	Merge	Rear
30	A	M	FALSE
31	B
32	C
33	D
34	E
35	F
36	G
37	H	G
38	I
39	J
40	K
41	L	C
42	M
Sheet1

 

[DanteAmor]

sheet1 column P is tickboxes (True/False for that column)

What kind of "tickboxes" are in column P?
Are they UserForm Control or ActiveX Control?
Are they linked to a cell? (They should be linked to a cell, for example the "tickbox" of cell P30 linked to cell Q30, this way the verification would be simpler)

 

[B-Man]

What kind of "tickboxes" are in column P?
Are they UserForm Control or ActiveX Control?
Are they linked to a cell? (They should be linked to a cell, for example the "tickbox" of cell P30 linked to cell Q30, this way the verification would be simpler)

Not sure what type tbh. 90% sure active
They are linked to the cell they hover over so tickbox in p30 is p30 with white font

 

[B-Man]

I ended up moving my list to N3:P15

I found and modified code to work had a few work arounds but it does what I need to do.
had to have column O mirrored image of N then do the changes rather than having blanks in column O


I'm looking to add the tickbox section but struggling.
basically if its ticked in ws2.column P (cell = true) then lookup ws2.column N in ws1.column D and replace value in ws1.column F with "cost"


this is part of a bigger code so I have extracted this section

Sub Merge()

    Dim wb As Workbook
    Set wb = '''''''''''''''workbook I want to modify'''''''


    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim oCell As Range
    

    
    Dim i As Long
    i = 2
    
    Set ws2 = ThisWorkbook.Sheets("Sheet1")
    Set ws1 = wb.Sheets("Costs")
  

    '''''''''''''''''''''''''''''''''''''''''''''
    'add tickbox stuff here?
    ''''''''''''''''''''''''''''''''''''''''''''''
    
    
    Do While ws1.Cells(i, 4).Value <> ""
        Set oCell = ws2.Range("N3:N15").Find(What:=ws1.Cells(i, 4))
        If Not oCell Is Nothing Then ws1.Cells(i, 4) = oCell.Offset(0, 1)
        i = i + 1
      Debug.Print ws1.Cells(i, 4).Value
    Loop
    
    Set wb = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing

End Sub

 

[DanteAmor]

basically if its ticked in ws2.column P (cell = true) then lookup ws2.column N in ws1.column D and replace value in ws1.column F with "cost"

I'm not sure if it's really what you need, but according to your instruction the following macro does it.
If it is not what you need, it would be easier to understand if you explain it with images, what you have on each sheet and what you should have after executing the macro.
By the way, I added the "book1.xlsx" to set to wb.

Sub Merge()
    Dim wb As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim oCell As Range, f As Range
    Dim i As Long
    
    Set ws2 = ThisWorkbook.Sheets("Sheet1")
    Set wb = Workbooks("Book1.xlsx")
    Set ws1 = wb.Sheets("Costs")

    '''''''''''''''''''''''''''''''''''''''''''''
    'add tickbox stuff here?
    ''''''''''''''''''''''''''''''''''''''''''''''
    'basically if its ticked in ws2.column P (cell = true) then
    'lookup ws2.column N in ws1.column D and
    'replace value in ws1.column F with "cost"
    
    i = 2
    Do While ws2.Range("N" & i).Value <> ""
      If ws2.Range("P" & i).Value = True Then
        Set f = ws1.Range("D:D").Find(ws2.Range("N" & i).Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          ws1.Range("F" & f.Row).Value = "cost"
        End If
      End If
      i = i + 1
    Loop
    
'    i = 2
'    Do While ws1.Cells(i, 4).Value <> ""
'        Set oCell = ws2.Range("N3:N15").Find(What:=ws1.Cells(i, 4))
'        If Not oCell Is Nothing Then ws1.Cells(i, 4) = oCell.Offset(0, 1)
'        i = i + 1
'      Debug.Print ws1.Cells(i, 4).Value
'    Loop
    
    Set wb = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

 

[B-Man]

thanks I will have a look tonight and see how it goes
I knew I could sort of copy my other formula but I wasn't having much luck.

 

[B-Man]

didn't quite work how I wanted.
it done the first one but was looping through the tickbox section each go aswell.

between yours and the other loop function I had I got it to work.
then after finally half understanding the

If Not  Is Nothing Then

part, I managed to combine the 2 loops into one and works a treat.

thanks for helping

code attached for anyone following also changed i to J as i had 2 loops going and didn't want them to be the same
also probable doesnt need the .value but was having issues and changed alot of things then worked out I had the wrong offset number

j = 2
   Do While ws1.Cells(j, 4).Value <> ""
        Set oCell = ws2.Range("N3:N15").Find(What:=ws1.Cells(j, 4))
        If Not oCell Is Nothing Then ws1.Cells(j, 4) = oCell.Offset(0, 1)                      
        If oCell.Offset(0, 2).Value = "True" Then ws1.Cells(j, 6).Value = "cost"       
        j = j + 1
    Loop

 

[DanteAmor]

basically if its ticked in ws2.column P (cell = true) then lookup ws2.column N in ws1.column D

That's what you asked for, look for "N" inside "D"
That does my code:
Set f = ws1.Range("D:D").Find(ws2.Range("N" & i).Value

Your code does exactly the opposite, that way it is difficult to deliver a correct solution.
If your request says one thing and you want another ?.
Set oCell = ws2.Range("N3:N15").Find(What:=ws1.Cells(j, 4))

But I'm glad to hear that you found a solution ?

 

[B-Man]

Yes, yes it does.
As if it goes the other way it loops through the list not the data. (ie only finding the first one of each.)
My poor explanation of what I wanted and how excel works.

I wanted it to lookup the value in the list and find/change any matching ones in the data.
This has the same outcome just looks up the data to find the item in the list and changes the matching ones.