LinkBack URL
About LinkBacks
Title says it all, what is the code to switch between R1C1 coordinates, and A1?
Title says it all, what is the code to switch between R1C1 coordinates, and A1?
and
application.ReferenceStyle = xlR1C1
application.ReferenceStyle = xlA1
Everyone who confuses correlation and causation ends up dead.
i cant get it to work thoughm what im trying to do is
in the first free column i want to paste som data, and further down i need to get just the 2 first letters in the row 25 lines up.HTML Code:
However i dont think the left function exists in vba, is there anything similar i can use?
The Left function does exist in VBA but you're trying to use it like a formula. You either need to enter a formula into the cell, or calculate the row number in VBA. Neither requires you to switch the reference style.
or:wb.Sheets("Data").Cells(26, Columns.Count).End(xlToLeft).Offset(, 1).FormulaR1C1 = "=Left(R[-25]C,2)"
wb.Sheets("Data").Cells(26, Columns.Count).End(xlToLeft).Offset(, 1).Value = Left(Cells(1, Cells(26, Columns.Count).End(xlToLeft).Column).Value, 2)
that worked! thanks. however next step i need to check if the number in that cell is 01,04,07, or 10. Assigning Q1,Q1,Q3, and Q4 respectively.
I dont understand this very well, why wont this work ? im sure its not efficient or whatever, but i dont see why it keeps stoppingHTML Code:
You need to double up quotes in the formula string, and you need to use commas rather than semicolons as separators (VBA wants US format):
wb.Sheets("Data").Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).FormulaR1C1 = "=IF(R[24]C=""01"",""Q1"",(IF(R[24]C=""04"",""Q2"",IF(R[24]C=""07"",""Q3"",IF(R[24]C=""10"",""Q4"",NA())))))"
for anyone who switches r1c1 and A1 style frequently I thoroughly recommend creating an adding to rebuild the formula tab that contains a toggle button to switch modes. whilst you are at it, create a button for the sheet calculation menu that calculates the selected range only. I use these two very frequently.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks