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

robertpri

New Member
Joined
Dec 16, 2004
Messages
28
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Re: "If" [cell] "then" insert this [cell

Maybe:

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

How many conditions do you have?

Smitty
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top