VBA in Excel 2

[Sadukar]

I normally use VBA in Access. I have just started doing a projet in Excel.

How do I use VBA in Excel?

For example:
I want to check if a range from c4:z4 has a value of "1". If yes I want to change the value to 2. If not I want to delete the value in the cell.
I tried
Command 1
If me.cells.text = "1" then
Me.cells.text = 3
Else
Me.cells.text = Null
End if

Thanks for the help
S.

 

[chandlm]

Sadukar,

You could try using this


sub checkcellvalue()
For Each cell In Range("c4", "z4&quot
If cell.Value = "1" Then
cell.Value = "2"
Else
cell.Value = ""
End If

Next
end sub

HTH



Matt

 

[Sadukar]

Thanks Matt
Worked perfect

Would you mind looking at this problem for a second?

I want to check if range c4:z4 has a value of "1"
If a cell does contain "1" then go to 4th cell to the right and paste a value of 1.
For example
f4 has a VAULE OF "1" then
Paste value of "1" into J4 (Interval of 4 cells)

Do you understand what I am looking for?


Thanks
S.

 

[DrBowes]

For i = 26 To 3 Step -1
If Cells(4, i).Value = "1" Then _
Cells(4, i + 4).Value = "1"
Next i

 

[Sadukar]

Thanks Dr.
Work perfect

 

[Sadukar]

Sorry if I am asking stupid questions but I am a bit lost when it comes to Excel. Could you take a quick look at this for me?

For Each Cell In Range("f5", "be5&quot
If Cell.Value = "R" Then
Range("CELL TO THE RIGHT OF WHERE R IS", "BE4&quot.Select
Selection.Copy
Range("CELL TO THE RIGHT OF WHERE R IS", "BE5&quot.Select
ActiveSheet.Paste

How can I paste in the Cell to the right of "R".
I want to copy and paste everything to the right of the value R in the row.

Thanks for your time,
S.

 

[SkipVought]

For Each Cell In Range("f5:be5")
  If Cell.Value = "R" Then
    Range(Cell.Offset(-1,1), Cells(4, "BE")).Copy _
      Destination:=Cell.Offset(0,1)
  End If
Next

I am assuming that you want to copy from ROW 4 beginning at the cell to the RIGHT of Cell and paste in ROW 5.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sadukar]

Thanks Skip

Just one more thing,

Is there something I can add if there is more than one "R".
For example if f5 had a "R" and p5 had a "R". The code I have would paste over the p5 value.
Can I check the row and see where the last "R" is?

Thanks for you time
S.

 

[chandlm]

Sadukar,

this should run through the range and store the local address of the last instance of the cell containing an "R"
If it doesn't find an instance of "R" then it won't display anything.

Don't forget that it is case sensitive


sub findlastcell()
dim msg as string
For Each cell In Range("f5:be5&quot
If cell.Value = "R" Then
msg = cell.AddressLocal
End If
Next
if msg <>&quot;&quot;then MsgBox msg

end sub


HTH

Matt

 

[Sadukar]

Works
Where can I incorporate this (below) into the above code?

Range(Cell.Offset(-1, 1), Cells(4, &quot;BE&quot).Copy Destination:=Cells.Offset(0, 1)

I want the Cell = msg so that info is copied and Pasted from right of LAST &quot;R&quot;.

Thanks for all the help.
S.

 

[SkipVought]

Sub findlastcell()
    Dim msg As String
    For Each C In Range("f5:be5")
      If C.Value = "R" Then
        msg = C.Address
      End If
    Next
    If msg <> &quot;&quot; Then
        Range(Range(msg).Offset(-1, 1), Cells(4, &quot;BE&quot;)).Copy _
          Destination:=Range(msg).Offset(0, 1)
    End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sadukar]

Thanks Skip and Matt
You were both very helpful.

 

[chandlm]

No Problem.

Thanks for the Star.


Matt