Yes, sent you a PM
VBA to find column header through Msg Box
- Hanes109
- Thread is marked as Resolved.
-
-
-
Hi KjBox, hope you're doing good..
I'm getting error when running this code and found the root cause as "Header is not string". Its actually value.
Please help.
Thanks!!
Code
Display MoreOption Explicit Sub AddData() Dim x, xx, y, yy, i As Long, ii As Long, s As String, sSht As String Dim sInput As String, wbk As Workbook, ws As Worksheet Set wbk = Workbooks("S1") 'CHANGE AS REQUIRED sInput = InputBox("Enter Sheet, Header, Amount, Currency (separated by comma & a space)", "Data Entry") If sInput <> "" Then y = Split(sInput, ", ") sSht = y(LBound(y)) Set ws = wbk.Sheets(sSht) With ws.Cells(1).CurrentRegion x = .Value xx = .Rows(1) i = Application.Match(y(LBound(y) + 1), xx, 0) For ii = 2 To UBound(x, 1) x(ii, i) = Format(CDbl(y(LBound(y) + 2)), "#.00") x(ii, i + 1) = y(UBound(y)) Next .Value = x End With End If End Sub -
Attach your workbook which is giving error
-
-
-
-
Try the attached.
I have done away with the Input Box. Enter the required updating data in cells C3 to C6 on the Input sheet.
There is no need to have a list of the sheet names, the Data Validation List for C3 will update according to the actual workbook sheets as soon as C3 is selected, the C4 Data Validation List will update according to the Sheet selection.
The Currency can be entered as lower or upper case (AED or aed), the code will convert it to Upper Case
Note C3 to C6 are now Named Ranges.
There is code in the Worksheet Object Module for Input Sheet to updating the Data Validation.
-
It works perfectly. Thank you so much...!!!
-
You're welcome
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!