"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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
Joined
Nov 4, 2003
Messages
960
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
Joined
Dec 16, 2004
Messages
28
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
Joined
Nov 4, 2003
Messages
960
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
Joined
Dec 16, 2004
Messages
28
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"
 

Forum statistics

Threads
1,148,229
Messages
5,745,483
Members
423,953
Latest member
MrC54

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
Top