Selecting a range : New Question

S

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
 
S

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 ¹üÀ§¿¡¼­ ¸®½ºÆ® ¹Ú½ºÀÇ
Click to expand...
³»¿ëÀ» ã¾Æ¼­, ±×
 
S

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
 
P

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
 
D

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


Suh Suk Ho said:
Dear Lance;

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

Come on!

Suh, Suk Ho
Click to expand...
[clipped]
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting a range 2
Loop Repeats - PLS HELP 2
Excel hangs 1
Create Detail Sheets from Pivot Table 1
VBA: Attach a variable to a range after executing Find command 1
Error in vba line 7
Named Range maker code 9
COPY PASTE with FILTER copies anti-filtered results as hidden rows 2

Top