"If" [cell] "then" insert this [cell]

robertpri

New Member
Thanks to this site, life is becoming easier.
Many thanks to all.

Again, 1,000's of rows.

Need a script to insert a certain number in B if a certain number exists in A

Actually, these are "non-add" part numbers, not really values, but I don't know how else to describe it. [text?]

If A:A=12345, then insert 5678 in B in same row
If A:A=4567, then insert 49509 in B in same row

thanks again!

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Smitty

Legend
Re: "If" [cell] "then" insert this [cell

Maybe:

=IF(A1=12345,5678,IF(A1=4567,49509,""))

How many conditions do you have?

Smitty

martinee

Well-known Member
Try this: (Note: Not tested):
Code:
``````Sub tt()
'declarations
Dim c As Range, rng As Range

Set rng = Range("A2:a" & Range("a65536").End(xlUp).Row)

For Each c In rng
Select Case c.Value
Case "12345"
c.Offset(0, 1).Value = "678910"
Case "11111"
c.Offset(0, 1).Value = "22222"
'continue as neccisary
Case Else
'this happens when A is not equal to
'any of the values you want
End Select
Next c

End Sub``````
Change the values to suite. HTH.

robertpri

New Member
Re: "If" [cell] "then" insert this [cell

This not only solved it [thanks!] but I finally have a basic feel for the confusing offset command.

So,

Case "11111"
c.Offset(0, 1).Value = "22222"

means if the value of 11111 in the first cell 0, then insert the value 22222 in the offset cell, or one to the right: ergo, 0,1

This is great stuff! So I could insert the value in colum D by using 0,4.

I think

martinee

Well-known Member
Actually, if you were to use something like:

Range("A1").Offset(1,1).Value = 2

Then the value of 2 would appear in cell B2. The syntax for offset goes like this:

Offset(#rows to move, #of columns to move)

where a positive number moves down or right, and a negative number will move up or left (respectively). So in the example, we started with cell A1, then moved left 1 cell and down 1 cell and hence the resulting cell was B2.

Hope this clears things up for you. HTH.

robertpri

New Member
Re: "If" [cell] "then" insert this [cell

Understood on the offsets. Experimented and it works.

Now, just to add one more column.
What if I want to add a second condition or value?

Column A "12345"
and
Column B "red"
= "678910" offset (0,4) or some cell?

Case "12345"
c.Offset(0, 1).Value = "678910"

Replies
0
Views
542
Replies
2
Views
413
Replies
3
Views
186
Replies
1
Views
214
Replies
6
Views
334

1,181,375
Messages
5,929,585
Members
436,681
Latest member
natalie123vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back