Macro to replace value in a column

[maria_G]

Hi guys! New to the forum! Need help please!

I have a macro that pulls in data from a SharePoint List. I then want to update a value in a column but it's not updating. No errors or anything, values are just not getting updated.

Sub Refresh_From_SharePoint()
'
Sheets("Data").Select
' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("Data").ListObjects("Table_C_GRV").QueryTable.Refresh BackgroundQuery:=False
'
Call Update_Approval
Sheets("Approvals").Select

End Sub

Sub Update_Approval()
'this will get you the last row number in column "L"
lastRow = Sheets("Data").Cells(Sheets("Data").Rows.Count, "L").End(xlUp).Row

'loop through all cells in column "L" and replace text as needed
For i = 1 To lastRow
Sheets("Data").Cells(i, 2).Value = Replace(Sheets("Data").Cells(i, 2).Value, "TRUE", "Approved")
Sheets("Data").Cells(i, 2).Value = Replace(Sheets("Data").Cells(i, 2).Value, "FALSE", "UnApproved")
Next i
End Sub

[ByteMarks]

I think the reason is that .Cells(i,2) is looking at column 2, i.e. B. L would be column 12

You could probably remove the loop and do replace instead.

Please Login or Register  to view this content.

[maria_G]

Thank you so much! That worked!

[maria_G]

Hi Again,
They changed it on me again and I can't get it to work properly. Now, instead of replacing a value, they want to replace it or update it based on fields that are populated.

So based on columns populated, the status should change accordingly:
1. If columns are populated and status is TRUE - change to APPROVED
2. If columns are populated and status is FALSE - change to UNAPPROVED
3. If columns are not populated and status is FALSE - change to PENDING

[ByteMarks]

Maybe

Please Login or Register  to view this content.

[maria_G]

Sorry, I should've started a new thread....the last question didn't work for me. The thread for updating a column worked fine. Thanks for your help!