Writing VBA code for Excel Spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2409746%22%20slang%3D%22en-US%22%3EWriting%20VBA%20code%20for%20Excel%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2409746%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20writing%20a%20code%20in%20vba%20for%20an%20excel%20spreadsheet%20that%20takes%20an%20array%20of%20dates%20for%20a%20particular%20sheet%2C%20and%20iterates%20through%20the%20array%20for%20each%20row%20of%20the%20sheet.%20If%20the%20code%20finds%20the%20the%20date%20on%20the%20row%20matches%20a%20date%20in%20the%20array%2C%20it%20will%20input%20into%20the%20row%20the%20price%20that%20is%20listed%20in%20a%20parallel%20array.%20The%20code%20works%20fine%2C%20but%20every%20time%20the%20code%20inputs%20a%20price%20with%20decimal%20places%2C%20the%20decimal%20places%20are%20eliminated.%20It%20is%20not%20rounding%20because%20if%20a%20price%20is%20put%20in%20as%2C%20for%20example%2C%202214.5%2C%20the%20sheet%20will%20read%202214.%20I%20included%20the%20whole%20code%2C%20but%20I%20sent%20a%20snip%20of%26nbsp%3B%20the%20loop%20that%20interates%20and%20inputs.%20I%20also%20included%20a%20snip%20of%20the%20sheet%2C%20to%20show%20that%20it%20is%20doing%20what%20I%20say%20it%20is%20doing.%20I%20thought%20it%20might%20be%20a%20data%20type%20issue%2C%20so%20I%20changed%20all%20of%20my%20arrays%20from%20integers%20to%20longs%2C%20but%20that%20didn't%20work.%20Also%2C%20it%20doesn't%20show%20the%20full%20number%20in%20the%20formula%20bar%20if%20I%20click%20on%20the%20cell%2C%20it%20just%20simply%20inputs%20the%20wrong%20value.%20What%20can%20I%20do%20to%20resolve%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2409746%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2409926%22%20slang%3D%22en-US%22%3ERe%3A%20Writing%20VBA%20code%20for%20Excel%20Spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2409926%22%20slang%3D%22en-US%22%3EChanging%20the%20price%20amount%20array%20data%20type%20from%20integer%20to%20long%20won't%20fix%20the%20problem%20(long%20is%20still%20an%20integer).%20You%20need%20to%20use%20a%20data%20type%20for%20the%20array%20containing%20price%20amounts%20that%20supports%20decimals%2C%20such%20as%20single%20or%20double.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am writing a code in vba for an excel spreadsheet that takes an array of dates for a particular sheet, and iterates through the array for each row of the sheet. If the code finds the the date on the row matches a date in the array, it will input into the row the price that is listed in a parallel array. The code works fine, but every time the code inputs a price with decimal places, the decimal places are eliminated. It is not rounding because if a price is put in as, for example, 2214.5, the sheet will read 2214. I included the whole code, but I sent a snip of  the loop that interates and inputs. I also included a snip of the sheet, to show that it is doing what I say it is doing. I thought it might be a data type issue, so I changed all of my arrays from integers to longs, but that didn't work. Also, it doesn't show the full number in the formula bar if I click on the cell, it just simply inputs the wrong value. What can I do to resolve this?

1 Reply
Changing the price amount array data type from integer to long won't fix the problem (long is still an integer). You need to use a data type for the array containing price amounts that supports decimals, such as single or double.