0

I am trying to create a VBA code that will pull the info to the Left (eventually the right) of a Hyphen based on a range and a cell given by the user. EX:

enter image description here

Result A & Result B are what I am trying to get too. I've tested everything in my code until this part and it all works. The entire thing works if I physically type in a cell address (i.e $D2 - I will need the column absolute, but the row relative so that it moves with the range selection). I just can't get it to work with the user input of the "Starting" variable. I need it to be user input because this code will be used on sheets set up completely different than this one. There is a good chance I am missing something obvious but I'm not seeing it @_@. Any suggestions?

**Sorry in advance for the long lines of code

Private Sub Seperate_XtoY_Click()

Dim iCol As Long
Dim iCount As Long
Dim i As Long
Dim Smaller As Range
Dim Bigger As Range
Dim Starting As Range

'Get number of columns that you want to insert with a user input box
iCount = InputBox(Prompt:="How many columns you want to add?")

'Get column NUMBER where you want to insert the new column
iCol = InputBox _
(Prompt:= _
"BEFORE which column do you want to add the new column(s)? (Enter the column number i.e A=1, B=2, C=3, etc)")

'loop to insert new column(s)
For i = 1 To iCount
    Columns(iCol).EntireColumn.Insert
Next i

'Makes range variable "Starting" equal to the user input of a range (in this case just 1 cell)
Set Starting = Application.InputBox("Select the FIRST cell of the Original Range of #'s", "Obtain Range Object", Type:=8)
'Makes range variable "Smaller" equal to the user input of a range (where the info will actually populate)
Set Smaller = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

Smaller.Formula = "=IF(ISNUMBER(SEARCH(""½"", & Starting.Address(0, ""$"") &)),""0.5"",IF(ISNUMBER(SEARCH(""¼"",& Starting.Address(0, ""$"") &)),""0.25"",IF(ISNUMBER(SEARCH(""¾"",& Starting.Address(0, ""$"") &)),""0.75"",LEFT( &Starting.Address(0, ""$"")&, FIND(""–"",& Starting.Address(0, ""$"")&)-1))))"

End Sub
user85352
  • 29
  • 1
  • 8
  • 2
    Any VBA (such as `Starting.Address`) needs to be concatenated into the formula string with `&`, and should not fall inside the quotes. – BigBen Mar 24 '22 at 18:10
  • 2
    Also check the parameters of [`Range.Address`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.address). – BigBen Mar 24 '22 at 18:11
  • To make sure I understand what you mean, do you mean I would need to interrupt the formal at the comma, add something like this "Starting.Address(xlR[1]C1)" then continue the formula" – user85352 Mar 24 '22 at 18:25
  • 2
    No, you need something like `"=IF(ISNUMBER(SEARCH(""½""" & Starting.Address(....) & "restofformula..."`. See [this](https://stackoverflow.com/questions/42503316/how-can-i-insert-variable-into-formula-in-vba). – BigBen Mar 24 '22 at 18:28
  • Okay I really thought this would be it, but I still get an error `Smaller.Formula = "=IF(ISNUMBER(SEARCH(""½"", & Starting.Address(RowAbsolute:=False) &)),""0.5"",IF(ISNUMBER(SEARCH(""¼"", & Starting.Address(RowAbsolute:=False) &)),""0.25"",IF(ISNUMBER(SEARCH(""¾"",& Starting.Address(RowAbsolute:=False) &)),""0.75"",LEFT(& Starting.Address(RowAbsolute:=False) &, FIND(""–"", & Starting.Address(RowAbsolute:=False) &)-1))))"` – user85352 Mar 24 '22 at 18:45
  • Hint: instead of writing the formula to a cell, `Debug.Print` it to the Immediate Window, and then try pasting that output into a cell - you should be able to spot errors easier. – BigBen Mar 24 '22 at 18:49
  • Sadly I think I'm still missing something. When I did the debugger it's saying "object required." From what I understand, that means I have declared my "Starting" variable wrong, but I thought by "setting" it I made it the type of variable that I want. In fact if I print it, it's exactly how I want it. So I tried doing this `"=IF(ISNUMBER(SEARCH(""½""," & Starting.Address(ReferenceStyle:=xlR1C1, RowAbsolute:=False) & ")),` Where I legit removed it from the quotes, and same error. It looks like I'm doing EX 2 from [This](https://www.educba.com/vba-object-required/)but I'm not sure how to fix it – user85352 Mar 24 '22 at 19:37

1 Answers1

0

It turns out I had the right idea based on my last comment. I did need remove the variable completely out of the quotes (and then restart them), double check the placing of where I put those quotes, and use a different version of the .Address function to make only my column an absolute reference. All the other lines of code from above were all good, it was just the final line that needed to changed. Thank you @BigBen for giving me a push in the right direction. Looking at the program with fresh eyes also helped lol.

Smaller.Formula = "=IF(ISNUMBER(SEARCH(""½""," & Starting.Address(RowAbsolute:=False) & " )),""0.5"",IF(ISNUMBER(SEARCH(""¼"", " & Starting.Address(RowAbsolute:=False) & ")),""0.25"",IF(ISNUMBER(SEARCH(""¾"", " & Starting.Address(RowAbsolute:=False) & " )),""0.75"",LEFT( " & Starting.Address(RowAbsolute:=False) & " , FIND(""–"", " & Starting.Address(RowAbsolute:=False) & ")-1))))"

I also got the Right side function working too if anyone is interested:

Bigger.Formula = "=IF(ISNUMBER(SEARCH(""– ½""," & Starting.Address(RowAbsolute:=False) & ")),""0.5"",IF(ISNUMBER(SEARCH(""– ¼""," & Starting.Address(RowAbsolute:=False) & ")),""0.25"",IF(ISNUMBER(SEARCH(""– ¾""," & Starting.Address(RowAbsolute:=False) & ")),""0.75"",RIGHT(" & Starting.Address(RowAbsolute:=False) & ",LEN(" & Starting.Address(RowAbsolute:=False) & ")-FIND(""– ""," & Starting.Address(RowAbsolute:=False) & ")-1))))"

PLEASE NOTE for anyone who may want to use a variation of my code I used a slightly bigger hyphen than the typical hyphen ("-" vs "–")

user85352
  • 29
  • 1
  • 8