Selecting a range : New Question

[Suh Suk Ho]

Dear Colleagues;

Thank you for the answer last time about selecting a range even though I'm
still stuck with it and sitting on it.

I'm trying to select a cell right next to a cell in a named range as in the
following context;


For each cell in NamedRange

If cell.Value= A_Value_from_a_ListBox Then

---> Select a CELL(or a Range) right next to cell

End If

Next

I tried the followings and they all didn't work;

- cell.Offset(0,1).Select
- ActiveCell.Offset(ActiveCell.Row, ActiveCell.Column + 1).Select
- ActiveCell.Offset(0, 1).Select

And there's nothing wrong with the NamedRange.

The code at the bottom is the code I'm having trouble with.

Please help.

And what is that ActiveCell anyway? Isn't that supposed to be the one that
is being, what do call it, stepped through for example by For.. Next loop?

As you can see, my level of VBA is very poor, even though I've been learning
quite a while. Please help, please!

Yours Sincerely,

Suh, Suk Ho




Private Sub CommandButton1_Click()


'ÀÏ´Ü ¼¼ºÎ»ç¾çÀÌ µé¾îÀÖ´Â ³»¿ëÀ» ¸ðµÎ Áö¿î´Ù.

Dim RowCount As Single
'¿©±â¿¡ ¸¸¾à¿¡ a25¿¡ ¾Æ¹«·± ³»¿ëÀÌ µé¾îÀÖÁö ¾Ê´Ù¸é °Ç³Ê ¶Ú´Ù.

If Sheets("Quotation").Range("a25").Value <> "" Then
RowCount = Sheets("Quotation").Range("a25").CurrentRegion.Rows.Count

Sheets("Quotation").Range("a25:j" & 25 + RowCount & "").Select

Selection.Delete Shift:=xlUp
MsgBox "Why in the hell are you here"
End If

Dim Cell_A25 As Single 'a25¹ø ¼¿¹øÈ£

Cell_A25 = 25


For i = 0 To frmModelSelect.lboxRight.ListCount - 1

With Sheets("Quotation")

.Range("a" & Cell_A25 & ":I" & Cell_A25 & "").Select
Selection.Insert Shift:=xlDown


'¼¿À» Æ÷¸Ë½ÃŲ´Ù.

.Range("a" & Cell_A25 & ":d" & Cell_A25 & "").Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
.Range("H" & Cell_A25 & ":I" & Cell_A25 & "").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
.NumberFormatLocal = "$#,##0.00"
End With
End With '½¬Æ®


' ¸®½ºÆ®¹Ú½ºÀÇ ³»¿ëÀ» ²¨³»¼­ ±× ³»¿ëÀ» Specifications ½¬Æ®ÀÇ ³»¿ë°ú
ºñ±³ÇÏ¿©, ä¿ö³Ö´Â´Ù.

' 1. SpecificationÀÇ ModelName ¹üÀ§¿¡¼­ ¸®½ºÆ® ¹Ú½ºÀÇ ³»¿ëÀ» ã¾Æ¼­, ±×
³»¿ëÀÇ ¹Ù·Î ¿·Ä­ÀÇ ³»¿ëÀ» º¹»çÇؼ­

For Each cell In Sheets("Specifications").Range("ModelName")

If cell.Value = frmModelSelect.lboxRight.List(i) Then


MsgBox "cell.value : " & cell.Value
MsgBox "frmModelSelect.lboxRight.List(i) : " &
frmModelSelect.lboxRight.List(i)
MsgBox "cell.Offset(0, 1).Value : " & cell.Offset(0, 1).Value
MsgBox "ActiveCell.Value :" & ActiveCell.Value

' Sheets("Specifications").Range("" & ActiveCell.Offset(0, 1) & ":"
& ActiveCell.Offset(0, 5) & "").Select
'Sheets("Specifications").Range(Cells(ActiveCell.Row,
ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 5)).Select
'Sheets("Specifications").Range(Cells(cell.Row, cell.Column + 1),
Cells(cell.Row, cell.Column + 5)).Select

MsgBox "¼¿ ¿­: " & ActiveCell.Row & "¼¿ Çà : " & ActiveCell.Column &
""
cell.Offset(0, 1).Select
' ActiveCell.Offset(ActiveCell.Row, ActiveCell.Column + 1).Select
Selection.Copy

Sheets("Quotation").Range("A" & Cell_A25 & "").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

Next

Cell_A25 = Cell_A25 + 1



Next

Unload Me

End Sub

 

[Dave Peterson]

How about:

cell.offset(0,1).resize(1,4).copy

(Also at your first post)

 

[Suh Suk Ho]

Dear Lance;

I told you that "cell.Offset(0,1).Select" doesn't work.!!!

Come on!

Suh, Suk Ho

I create a column of the numbers 1-5, selected it and
called this macro, it placed the selection next to the #2

Sub testit()
Dim namedrange As Range
Dim cell As Range
Set namedrange = Selection
For Each cell In namedrange
If cell.Value = 2 Then
cell.Offset(0, 1).Select
End If
Next cell
End Sub

-----Original Message-----
Dear Colleagues;

Thank you for the answer last time about selecting a range even though I'm
still stuck with it and sitting on it.

I'm trying to select a cell right next to a cell in a named range as in the
following context;


For each cell in NamedRange

If cell.Value= A_Value_from_a_ListBox Then

---> Select a CELL(or a Range) right next to cell

End If

Next

I tried the followings and they all didn't work;

- cell.Offset(0,1).Select
- ActiveCell.Offset(ActiveCell.Row, ActiveCell.Column + 1).Select
- ActiveCell.Offset(0, 1).Select

And there's nothing wrong with the NamedRange.

The code at the bottom is the code I'm having trouble with.

Please help.

And what is that ActiveCell anyway? Isn't that supposed to be the one that
is being, what do call it, stepped through for example by For.. Next loop?

As you can see, my level of VBA is very poor, even though I've been learning
quite a while. Please help, please!

Yours Sincerely,

Suh, Suk Ho




Private Sub CommandButton1_Click()


'ÀÏ´Ü ¼¼ºÎ»ç¾çÀÌ µé¾îÀÖ´Â ³»¿ëÀ» ¸ðµÎ Áö¿î´Ù.

Dim RowCount As Single
'¿©±â¿¡ ¸¸¾à¿¡ a25¿¡ ¾Æ¹«·± ³»¿ëÀÌ µé¾îÀÖÁö ¾Ê´Ù¸é ° Ç³Ê ¶Ú´Ù.

If Sheets("Quotation").Range("a25").Value <> "" Then
RowCount = Sheets("Quotation").Range ("a25").CurrentRegion.Rows.Count

Sheets("Quotation").Range("a25:j" & 25 + RowCount & "").Select

Selection.Delete Shift:=xlUp
MsgBox "Why in the hell are you here"
End If

Dim Cell_A25 As Single 'a25¹ø ¼¿¹øÈ£

Cell_A25 = 25


For i = 0 To frmModelSelect.lboxRight.ListCount - 1

With Sheets("Quotation")

.Range("a" & Cell_A25 & ":I" & Cell_A25 & "").Select
Selection.Insert Shift:=xlDown


'¼¿À» Æ÷¸Ë½ÃŲ´Ù.

.Range("a" & Cell_A25 & ":d" & Cell_A25 & "").Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
.Range("H" & Cell_A25 & ":I" & Cell_A25 & "").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
.NumberFormatLocal = "$#,##0.00"
End With
End With '½¬Æ®


' ¸®½ºÆ®¹Ú½ºÀÇ ³»¿ëÀ» ²¨³»¼­ ±× ³»¿ëÀ» Specifications ½¬Æ®ÀÇ ³»¿ë°ú
ºñ±³ÇÏ¿©, ä¿ö³Ö´Â´Ù.

' 1. SpecificationÀÇ ModelName ¹üÀ§¿¡¼­ ¸®½ºÆ® ¹Ú½ºÀÇ

³»¿ëÀ» ã¾Æ¼­, ±×

 

[Suh Suk Ho]

Dear Dave;

I'm sorry that I didn't try your code last time. How foolish of me!

Your code worked!!!! Thank you!!!

Suh, Suk Ho

 

[Patrick Molloy]

doing this in a for...next loop will result in a cell being selected - but
only the last one that matches the test criterion, so it amy appear not to
work.
I'd say in general that one doesn't necessarily need to select a cell to use
it, eg

Sub Test()
Dim cell As Range
Dim NamedRange As Range
Set NamedRange = ThisWorkbook.Names("MyList").RefersToRange
For Each cell In NamedRange
If cell.Value = 3 Then
cell.Offset(0, 1).Value2 = "<----"
End If
Next
End Sub

 

[David McRitchie]

cell is just a variable name. I did not notice anything wrong
with your code, but Lance's code is a lot shorter (doesn't do
anything) but I think if you ran it, you would find that it works.
Options Explicit helps to keep you out of trouble with
variable usage, if you don't have it would suggest you use it.

Would be better if you can see something change, so modify
Lance's code and test with some specific test data.

place "B1" into cell B1 and use the fill handle to copy
down to B30, then select cells B2 to B29 and run this
macro based on Lance's. A macro should avoid
actually selecting cell and it is seldom necessary.

Option Explicit
Sub tester2()
Dim namedrange As Range
Dim cell As Range
'-- restore font color of ALL cells to black --
Cells.Selection.Font.ColorIndex = 0
Set namedrange = Selection
For Each cell In namedrange
'-- in adjacent cell add suffix to value and color the font
cell.Offset(0, 1).Value = cell.Value & "-SFX"
cell.Offset(0, 1).Font.ColorIndex = 55
Next cell
End Sub

A small test is a lot better demonstration than a larger test,
by the way I can't read your comments in your code with what
I presume to be double byte characters.

When posting a problem it helps if you can shorten the code
to something very small that fails rather than including all of the code.

Additional information on the above;
Colors: http://www.mvps.org/dmcritchie/excel/colors.htm
Test Data: http://www.mvps.org/dmcritchie/excel/join.htm#markcells
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dear Lance;

I told you that "cell.Offset(0,1).Select" doesn't work.!!!

Come on!

Suh, Suk Ho

[clipped]

 

[Dave Peterson]

You got another reply to your first post that cleaned up some other portions
(selects). You may want to see if that makes your code easier to understand.