vba to insert a new cell into a range based on user input

[MIKEBIZ]

Hello,


I'm new to VB and was looking for some help, any input would be greatly appreciated...


the cells A1 to H1 contain a bunch of values:

1 11 59 35 33 46 12 18

I need a code that allow the user to add a number to that list and also specify where it goes..

B3= what number to add
B4= after which number

example :

1 11 59 35 33 46 12 18

B3= 60
B4= 59



the result should look like this


1 11 59 60 35 33 46 12 18




Like I said, any help is greatly appreciated....thx

 

[Rick Rothstein]

Here is one way to do it...

Sub InsertValueFromB3AfterValueFromB4() Dim Addr As String Addr = Rows(1).Find(Range("B4"), Cells(1, Columns.Count - 1), xlValues, xlWhole, , xlNext).Offset(, 1).Address Range(Addr).Insert xlShiftToRight Range(Addr) = Range("B3").Value End Sub

 

[hiker95]

MIKEBIZ,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is another macro solution for you to consider. And, if the number is not found in row 1, you will get a message that the number was not found, and, the macro will terminate without an error.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	11	59	35	33	46	12	18
2
3		60	< what number to add
4		59	< after which number
5
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	11	59	60	35	33	46	12	18
2
3		60	< what number to add
4		59	< after which number
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub MIKEBIZ()
' hiker95, 08/23/2015, ME877731
Dim c As Range, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  If Not [B3] = vbEmpty And Not [B4] = vbEmpty Then
    n = Application.CountIf(.Rows(1), [B4])
    If n = 0 Then
      Application.ScreenUpdating = True
      MsgBox ("B4's value '" & [B4] & "' is not in row 1 - macro terminated!")
      Exit Sub
    ElseIf n > 0 Then
      If [A1] = [B4] Then
        [B1].Insert Shift:=xlShiftToRight
        [B1] = [B4]
      Else
        Set c = .Rows(1).Find([B4], LookAt:=xlWhole)
        .Cells(c.Row, c.Column + 1).Insert Shift:=xlShiftToRight
        .Cells(c.Row, c.Column + 1).Value = [B3]
      End If
    End If
  Else
    Application.ScreenUpdating = True
    MsgBox ("Cells 'B3' and 'B4' are empty - macro terminated!")
    Exit Sub
  End If
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the MIKEBIZ macro.

 

[Rick Rothstein]

hiker95 raises an excellent point in Message #3 about what to do if the item being searched for cannot be found Here is the code I posted earlier with the appropriate error trap placed in it...

Sub InsertValueFromB3AfterValueFromB4() Dim Addr As String On Error GoTo NotFound Addr = Rows(1).Find(Range("B4"), Cells(1, Columns.Count - 1), xlValues, xlWhole, , xlNext).Offset(, 1).Address Range(Addr).Insert xlShiftToRight Range(Addr) = Range("B3").Value Exit Sub NotFound: MsgBox "Could not find """ & Range("B4").Value & """ in Row 1." End Sub

 

[MIKEBIZ]

Thank you guys for the quick reply, the code Rick offered worked perfectly. I really appreciate the help.

i was wondering if you could provide me with a code to remove the numbers also. Once again I thank you in advance....

example :

1 11 59 35 33 46 12 18

D3= 35 (number to be removed)




the result should look like this


1 11 59 33 46 12 18

 

[Rick Rothstein]

Thank you guys for the quick reply, the code Rick offered worked perfectly. I really appreciate the help.

i was wondering if you could provide me with a code to remove the numbers also. Once again I thank you in advance....

example :

1 11 59 35 33 46 12 18

D3= 35 (number to be removed)

the result should look like this


1 11 59 33 46 12 18

I think tis macro will do what you want...

Sub DeleteValueFromD3() Dim Addr As String On Error GoTo NotFound Addr = Rows(1).Find(Range("D3"), Cells(1, Columns.Count - 1), xlValues, xlWhole, , xlNext).Address Range(Addr).Delete xlShiftToLeft Exit Sub NotFound: MsgBox "Could not find """ & Range("D3").Value & """ in Row 1." End Sub

 

[MIKEBIZ]

Thx again, works perfect!!!

 

[hiker95]

the code Rick offered worked perfectly.

MIKEBIZ,

Did you even try my macro?