Deleted
Deleted Member
Posts: 0
|
Post by Deleted on Nov 7, 2022 14:07:24 GMT
Hey chaps, I need some help, this is driving me crazy.
I am trying to macro something and just can't seem to get it working. I need the following:
Macro opens dialog box to choose a file. (CSV in this case) eg c:\data.csv
Save that file path into a cell (eg A1) (so file path is visible for audit to see where came from)
Then use that file name as part of a vlookup. eg vlookup(text,A1,......) inserting the file path in there.
First part is easy, just do getopenfilename and I can then paste that data. But I am not sure how to use that data to insert into the vlookup. When I paste the path manually it works, but errors when I try to reference it from cell A1. I am sure I am missing something obvious but my brain is not working this monday
|
|
crashV👀d👀
Junior Member
not just a game anymore...
Posts: 3,817
Member is Online
|
Post by crashV👀d👀 on Nov 7, 2022 15:48:15 GMT
FYI there is an excel thread ... fontgeek.io/thread/1013/stupid-ms-excel-problemIts been a while since I did some VBA but I'm sure to reference a specific cell on a specific sheet its Worksheets(sheetindex or name).range(cell ref).value Worksheets("Sheet1").Range("A1").Value - sheetname, cell ref Worksheets(1).Cells(1,1).Value - sheetindex, cell row/col Activesheet.Cells(1,1).Value - on current sheet is focused .Value can be dropped as it's the default Ahhh I think I've mistaken what you meant. Have you tried printing out what you're reading from the cell to make sure it's not being altered/truncated in anyway? |
|
|
|
Post by steifybobbins on Nov 7, 2022 21:09:34 GMT
Hey chaps, I need some help, this is driving me crazy. I am trying to macro something and just can't seem to get it working. I need the following: Macro opens dialog box to choose a file. (CSV in this case) eg c:\data.csv Save that file path into a cell (eg A1) (so file path is visible for audit to see where came from) Then use that file name as part of a vlookup. eg vlookup(text,A1,......) inserting the file path in there. First part is easy, just do getopenfilename and I can then paste that data. But I am not sure how to use that data to insert into the vlookup. When I paste the path manually it works, but errors when I try to reference it from cell A1. I am sure I am missing something obvious but my brain is not working this monday Do a cheat workaround. Have the cell reference as is but in a hidden cell use a formula to format the link as text for the purpose of the lookup |
|
|
|
Post by steifybobbins on Nov 7, 2022 21:10:14 GMT
Hey chaps, I need some help, this is driving me crazy. I am trying to macro something and just can't seem to get it working. I need the following: Macro opens dialog box to choose a file. (CSV in this case) eg c:\data.csv Save that file path into a cell (eg A1) (so file path is visible for audit to see where came from) Then use that file name as part of a vlookup. eg vlookup(text,A1,......) inserting the file path in there. First part is easy, just do getopenfilename and I can then paste that data. But I am not sure how to use that data to insert into the vlookup. When I paste the path manually it works, but errors when I try to reference it from cell A1. I am sure I am missing something obvious but my brain is not working this monday Do a cheat workaround. Have the cell reference as is but in a hidden cell use a formula to format the link as text for the purpose of the lookup or try xlookup which is way Less picky than vlookup |
|
