Cells selection

[maximus4004]

Hi,

I want to select cells when the range is under a certain number(<=0) and return value from other cells.

My problem is more how to use the proper syntax.

exemple:
If Range("J2:J44") >=0
    'Then ActiveCell = Select
'and return value in cell ("B2, C2") for each row.

 

[Desu Nota from Columbus]

Im confused, you want both b2 and c2 returned in every cell in the range J2:J44, if this range is >= 0?

Sub Beefy()

Dim i as integer

For i = 2 to 44

If range("J" & i).Value >= 0 Then
   Range("J" & i).FormulaR1C1 = "=R2C2&R2C3"

End IF

Next i

End Sub

 

[Desu Nota from Columbus]

The macro I wrote works as follows:

Example--

B2=beef
C2=cake

Put random numbers in J2:J44, but leave a couple blank.

Run the macro.

All cells in J2:j44 that previously had numbers will now say beefcake.

 

[maximus4004]

Hi Colombus

Thank you for the quick response. I try and it works.

I'd be more specific about my request.

If my CELLS <=0 in range ("J2:J44") = true

then return the value in colums "B" and "C" in that row

and concatenante them for one selection value.

I appreciate your help

 

[Desu Nota from Columbus]

What type of values can you expect to find in column J?

 

[Desu Nota from Columbus]

This isn't the best way of doing this, but it should let you see the syntax (I hope! )

Sub Ridic()

Dim i As Integer

For i = 2 To 44

If Range("J" & i).Value < 0 Or Range("J" & i).Value = "" Then
    Range("J" & i).Value = Range("J" & i).Value
Else
   Range("J" & i).FormulaR1C1 = "=RC[-8]&RC[-7]"
   
End If

Next i

End Sub

What this says is:

If any cell in the range J2:J44 is blank, or less than 0, do nothing.

If any cell in the range J2:J44 is greater than or equal to 0, or has text, combine the values of B and C in that row into the corresponding cell in Column J.

 

[maximus4004]

My value in "J" range is numerical and he give my quantity I will need to order.

My other cells are the PART # and LAMP # code I need to order.

When I need to place a order, that will give me e-mail with proper code to order.

 

[Desu Nota from Columbus]

How do you want your part # and lamp # to be joined?

For instance:

Part Number = 1234
Lamp Number = 5678

Current macro combines them as "12345678"

Could alter the line

Range("J" & i).FormulaR1C1 = "=RC[-8]&RC[-7]"

To make 1234-5678, 1234/5678, 1234 5678, Part # 1234/Lamp # 5678, etc... anything you want really.
</pre>