Menu
I have a table I need to fill with some information for each client job according to the client job ID, and I pull the info using vlookup within VBA.
Dim jobID as double, path as String, creationDate as date
For i = 2 to 100
jobID = Sheet11.Cells(i, 1).Value
path = Application.WorksheetFunction.VLookup(jobID, Sheet9.Range("A:G"), 7, False)
creationDate = Application.WorksheetFunction.VLookup(jobID, Sheet9.Range("A:M"), 13, False)
Sheet11.Cells(i, 6).Value = creationDate
Sheet11.Cells(i, 8).Value = path
Next i
The paths in sheet 9 look like "180,28,60,91,40,178" or "179,143,141,142,178" and has "General" format. The creation dates in sheet 9 look like "2015-11-12 11:44" and has yyyy-mm-dd h:mm custom date format. However, when the stored values were entered into sheet11 as shown, some of the paths were converted to numbers e.g. "179,143,141,142,178" became "179143141142178", although some stayed as string as I intended e.g. "180,28,60,91,40,178". Most dates also lost time information (h:mm info) and was entered in sheet11 in yyyy-mm-dd format, while some maintained original format.
Could someone help me maintain all the information from the date and prevent the path stored as string to become a number?
737
Run Macro When a Cell Changes (Method 1)Go to the VBA Editor (Alt + F11) and double-click the name of the spreadsheet that contains the cell that will change or just right-click the worksheet tab and click View Code.
If you want a user to specify a value to enter in a cell you can use an input box. Let's say you want to enter the value in cell A1, the code would be like this: Range("A1"). Value = _ InputBox(Prompt:="Type the value you want enter in A1.")
Not all VBA methods can be used with all types of references. For example, the “Value” method tells Excel to store a value in a relevant cell, or an array of values in a relevant range. If you stored a range of values in a variable, you cannot use the value method to attach that array to a single cell.
Use =Range(cell). Value function to access any cell in the worksheet. Assign the Age variable to it. Run your macro.
If you do not want to format the cells then here is the simplest way to do it
Sheet11.Cells(i, 8).Value = "'" & path
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
