VBA Find column by header name and autofill column from inputbox to the last row

Addictions

New Member
Joined
May 27, 2018
Messages
30
Hello,

I am trying to find VBA code which would find a header by name and then autofill that column from input box value to the last row.
Currently I am using below code to replace value in column PostalCode by value from inputbox. It replaces "United Kingdom" in Column PostalCode with value from inputbox. However I would want to autofill that column from inputbox instead of replacing it because value maybe different than "United Kingdom".


Sub Test()
Dim i As String
Dim k As String
Dim rngAddress As Range
i = "United Kingdom"
k = InputBox("Provide Postal Code")


Set rngAddress = Range("1:1").Find("PostalCode")
Range(rngAddress, rngAddress.End(xlDown)).Replace what:=i, replacement:=k, lookat:=xlPart, MatchCase:=False
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
How about
Code:
Sub Addictions()
   Dim Pcode As String
   Dim Fnd As Range
   
   Pcode = InputBox("Provide Postal Code")
   Set Fnd = Range("1:1").Find("PostalCode", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1).Resize(Range("A" & Rows.Count).End(xlUp).Row - 1).Value = Pcode
   End If
End Sub
 

Addictions

New Member
Joined
May 27, 2018
Messages
30
This is great. Thank you so much for such a quick help.

I also would want ask for help with one more thing.
The process is the same. The header name is "Custom SKU" and I would like to provide value to inputbox and column would autofill but with sequence instead.

Example:
input value is Image0001
Results:
Image0001
Image0002
Image0003
Image0004 etc
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
How about
Code:
Sub Addictions()
   Dim Pcode As String, Sku As String
   Dim Fnd As Range
   
   Pcode = InputBox("Provide Postal Code")
      Sku = InputBox("Provide SKU")
   Set Fnd = Range("1:1").Find("PostalCode", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1).Resize(Range("A" & Rows.Count).End(xlUp).Row - 1).Value = Pcode
   End If
   Set Fnd = Range("1:1").Find("Custom SKU", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1).Value = Sku
      Fnd.Offset(1).AutoFill Fnd.Offset(1).Resize(Range("A" & Rows.Count).End(xlUp).Row - 1), xlFillSeries
   End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,085,928
Messages
5,386,812
Members
402,019
Latest member
JLuby

Some videos you may like

This Week's Hot Topics

Top