How to insert Data
 

Hi, All

can you tell me, how create macro/vba to insert data to several row.
Which have following criteria:

if the data between first data and end data from column A, then that
data insert to column A
if the data between first data and end data from column B, then that
data insert to column B
if the data between first data and end data from column C, then that
data insert to column C
A B C D <- column for input data
1 10 22
4 12 25
5 15 26
6 17 40

if in column D I input 13 then the number that i have input insert to
column B

Regards


ARDI

How to insert Data
 

Ardi,

Try this. You need to add code to select data from column D. My
"test" routine calls the macro with a value (2 in the example).

I assume data starts in row 2. If this not true, change the rngA,B and C
statements.

HTH

Sub test()
Call InsertInColumn(2)
End Sub


Sub InsertInColumn(newValue)
Dim rngA As Range, rngB As Range, rngC As Range
Dim lastrow As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1")
With ws1
Set rngA = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set rngB = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Set rngC = Range("C2:C" & Cells(Rows.Count, "B").End(xlUp).Row)
End With
If newValue < Application.Max(rngA) Then
Call FindInsertRow(rngA, newValue)
Else
If newValue < Application.Max(rngB) Then
Call FindInsertRow(rngB, newValue)
Else
Call FindInsertRow(rngC, newValue)
End If
End If
End Sub

Sub FindInsertRow(rng, nv)
res = Application.Match(nv, rng, 1)
If IsError(res) Then res = 0
rng(res + 1).Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = nv
End Sub


"ardi" wrote:

Hi, All

can you tell me, how create macro/vba to insert data to several row.
Which have following criteria:

if the data between first data and end data from column A, then that
data insert to column A
if the data between first data and end data from column B, then that
data insert to column B
if the data between first data and end data from column C, then that
data insert to column C
A B C D <- column for input data
1 10 22
4 12 25
5 15 26
6 17 40

if in column D I input 13 then the number that i have input insert to
column B

Regards


ARDI

How to insert Data
 

NOTE: I have assumed no duplicates- if there are then change

If newValue < Application.Max(rngA) Then to

If newValue <= Application.Max(rngA) Then

Repeat for B and C



"Toppers" wrote:


Ardi,

Try this. You need to add code to select data from column D. My
"test" routine calls the macro with a value (2 in the example).

I assume data starts in row 2. If this not true, change the rngA,B and C
statements.

HTH

Sub test()
Call InsertInColumn(2)
End Sub


Sub InsertInColumn(newValue)
Dim rngA As Range, rngB As Range, rngC As Range
Dim lastrow As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1")
With ws1
Set rngA = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set rngB = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Set rngC = Range("C2:C" & Cells(Rows.Count, "B").End(xlUp).Row)
End With
If newValue < Application.Max(rngA) Then
Call FindInsertRow(rngA, newValue)
Else
If newValue < Application.Max(rngB) Then
Call FindInsertRow(rngB, newValue)
Else
Call FindInsertRow(rngC, newValue)
End If
End If
End Sub

Sub FindInsertRow(rng, nv)
res = Application.Match(nv, rng, 1)
If IsError(res) Then res = 0
rng(res + 1).Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = nv
End Sub


"ardi" wrote:

Hi, All

can you tell me, how create macro/vba to insert data to several row.
Which have following criteria:

if the data between first data and end data from column A, then that
data insert to column A
if the data between first data and end data from column B, then that
data insert to column B
if the data between first data and end data from column C, then that
data insert to column C
A B C D <- column for input data
1 10 22
4 12 25
5 15 26
6 17 40

if in column D I input 13 then the number that i have input insert to
column B

Regards


ARDI

How to insert Data
 

Sorry .. spotted a typo:

Set rngC = Range("C2:C" & Cells(Rows.Count, "B").End(xlUp).Row)

should be

Set rngC = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)


"Toppers" wrote:

NOTE: I have assumed no duplicates- if there are then change

If newValue < Application.Max(rngA) Then to

If newValue <= Application.Max(rngA) Then

Repeat for B and C



"Toppers" wrote:


Ardi,

Try this. You need to add code to select data from column D. My
"test" routine calls the macro with a value (2 in the example).

I assume data starts in row 2. If this not true, change the rngA,B and C
statements.

HTH

Sub test()
Call InsertInColumn(2)
End Sub


Sub InsertInColumn(newValue)
Dim rngA As Range, rngB As Range, rngC As Range
Dim lastrow As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1")
With ws1
Set rngA = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set rngB = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Set rngC = Range("C2:C" & Cells(Rows.Count, "B").End(xlUp).Row)
End With
If newValue < Application.Max(rngA) Then
Call FindInsertRow(rngA, newValue)
Else
If newValue < Application.Max(rngB) Then
Call FindInsertRow(rngB, newValue)
Else
Call FindInsertRow(rngC, newValue)
End If
End If
End Sub

Sub FindInsertRow(rng, nv)
res = Application.Match(nv, rng, 1)
If IsError(res) Then res = 0
rng(res + 1).Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = nv
End Sub


"ardi" wrote:

Hi, All

can you tell me, how create macro/vba to insert data to several row.
Which have following criteria:

if the data between first data and end data from column A, then that
data insert to column A
if the data between first data and end data from column B, then that
data insert to column B
if the data between first data and end data from column C, then that
data insert to column C
A B C D <- column for input data
1 10 22
4 12 25
5 15 26
6 17 40

if in column D I input 13 then the number that i have input insert to
column B

Regards


ARDI

How to insert Data
 

Thanks for your help