VBA Code that Enters a Note in a Column when another Column has some form of value/data in it

[bmkelly]

Hello,

I am in the middle of creating a bunch of macros and one thing I am stuck on is, how do I go about adding "Under Contract" in the Analyst Notes Column (T) when there is some form of data in the Vendor Contract Column (AD)? Not all thedata in Vendor Contract will be the same, its either blank or it has some contract code in the column and I need the ones that have a contract code to then enter the note in the Analyst Notes column.

Analyst Notes	Site Manager Notes	Corrections	SVC	Modality	EQStatus	Sub Status	InstalledDate	Ownership	WarrantyCode	VendorContract	VendorContractEnd
			$ 66,629.11	BIOMED	Active	Owned	2/15/2005​			TVC010919	11/30/2022​

Thanks!

 

[Fluff]

How about

Sub bmkelly()
   With Range("AD2", Range("AD" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(, -10).Value = "Under Contract"
   End With
End Sub

 

[bmkelly]

How about

Sub bmkelly()
   With Range("AD2", Range("AD" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(, -10).Value = "Under Contract"
   End With
End Sub

Thats perfect!....Is it too complicated/complex to have it read by column headers instead of columns. I only ask, because multiple users will be using this code in different spreadsheets and if they add columns then the code won't work accurately, correct?

Thank you again Fluff!

 

[Fluff]

How about

Sub bmkelly()
   Dim Vcol As Range, Acol As Range
   
   Set Vcol = Range("1:1").Find("VendorContract", , , xlWhole, , , False, , False)
   Set Acol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
   
   If Vcol Is Nothing Or Acol Is Nothing Then
      MsgBox "header is missing"
      Exit Sub
   End If
   With Range(Vcol, Cells(Rows.Count, Vcol.Column).End(xlUp))
      .SpecialCells(xlConstants).Offset(, Acol.Column - Vcol.Column).Value = "Under Contract"
   End With
End Sub

 

[bmkelly]

How about

Sub bmkelly()
   Dim Vcol As Range, Acol As Range
  
   Set Vcol = Range("1:1").Find("VendorContract", , , xlWhole, , , False, , False)
   Set Acol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
  
   If Vcol Is Nothing Or Acol Is Nothing Then
      MsgBox "header is missing"
      Exit Sub
   End If
   With Range(Vcol, Cells(Rows.Count, Vcol.Column).End(xlUp))
      .SpecialCells(xlConstants).Offset(, Acol.Column - Vcol.Column).Value = "Under Contract"
   End With
End Sub

That works! Thanks again Fluff! You have definitely came to save the day here numerous times! I have a few more I will probably be posting in new threads but once I Have two more codes done you wont have to be bothered by all my questions haha!

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[bmkelly]

Glad to help & thanks for the feedback.

I just noticed something actually.....When running that code it changes my Analyst Notes Column Header to "Under Contract" once I finish stepping thru this step-

" SpecialCells(xlConstants).Offset(, ACol.Column - VCol.Column).Value = "Review - Contract" "

 

[Fluff]

In that case use

With Range(Vcol.Offset(1), Cells(Rows.Count, Vcol.Column).End(xlUp))

 

[bmkelly]

In that case use

With Range(Vcol.Offset(1), Cells(Rows.Count, Vcol.Column).End(xlUp))

THANK YOU!

 

[Fluff]

Glad to help & thanks for the feedback.