Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1232to1236
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Two Variables

Two Variables
Marc
Hi VBA team,
I have following problem. I have 3 conditions which could appear in Cell A1 ( H1 2011/ H2 2011/ H1 2012 ).
And Cell B1 has one value like the numbers 1015634.
So if H1 2011 appears in A1 excel should search in C1:C10 for the value in B1. The row where it finds the value in C1:C10 for example in row 4 ( C4 ), should be copied then from D4 to F4.
If H2 2011 appears in A1 the search should happen for example in from C11:C20 and also the copy process.
The same when H3 2012 appears in A1.
I hope I was able to explain it correct.
Thanks for your help
Marc

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Two Variables
17.10.2011 07:31:19
Gerd
Hi Marc,
if the Value auf Range("A1") does not appear by Formula, you can copy this Eventprocedure into the modul of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngSearch As Range
If Target.Address = "$A$1" Then
Select Case Target.Value
Case "H1 2010": Set rngSearch = Range("C1:C10")
Case "H2 2011", "H1 2012": Set rngSearch = Range("C11:C20")
End Select
If WorksheetFunction.CountIf(rngSearch, Cells(1, 2).Value) > 0 Then
With rngSearch.Cells(WorksheetFunction.Match(Cells(1, 2).Value, rngSearch, 0), 1)
.Offset(0, 3).Value = .Offset(0, 1).Value
End With
End If
End If
End Sub
Greetz Gerd
Anzeige
AW: Two Variables
17.10.2011 17:45:36
Marc
Hi Gerd
thanks for the code. Somehow my excel is telling me error on the first line "If Target.Address = "$A$1" Then ". Do you know why?
I put H2 2011 in A1 ( no formula ) and 10135627 in B2 but the error comes frequently.
And if possible could you send me the code when the Range should be set in a different worksheet like " sheet 2 " ?
Thanks a lot
Marc
AW: Two Variables
17.10.2011 20:01:19
Gerd
Hi Marc,
I have no direct idea, cause this line of code was definitely correct.
The code i have written fast and made a short check with the compiler. It is surely not prepaird for all kinds of errors results for example in used cells are no values, in A1 is another value then you told and so on.
The only one is, that by this procedure you should first put in the value into B1 before you fill A1.
To your second question, pronounce another sheet:
Write before "rngSearch" Sheet2. or Worksheets("Sheets 2").
But I have no American Excel. So you can look in the project-window for the correct name.
I hope you are not disapeared. You have the opportunity to make an upload of your file and show a link.
Perheps another user with your Excel-version can look for it.
greetz gerd
Anzeige
AW: Two Variables
18.10.2011 01:02:12
Marc
https://www.herber.de/bbs/user/77056.xlsm
Hi Gerd,
please see above the link to the upload I did.
So basically A1 & B1 will change constantly. If A1 is H2 2011 it should look for B1 in C2:C10 and copy it to G1 for example.
The same for H1 2012 in A1 ( looking for B1 in D2:10 and copy it ).
The procedure should start when I press the button.
I copied your code to the Combo button, but somehow it is not working.
Thx for help!
BR Marc
AW: Two Variables
18.10.2011 06:58:51
hary
Hi Marc
One Way

Private Sub CommandButton1_Click()
Dim spalte As Range 'for find in Columns
Dim zeile As Range 'for find in Rows
Set spalte = Worksheets("Sheet1").Range("C1:E1").Find(What:=Range("A1"), LookIn:=xlValues,  _
lookat:=xlWhole)
Set zeile = Worksheets("Sheet1").Columns(spalte.Column).Find(What:=Range("B1"), LookIn:= _
xlValues, lookat:=xlWhole)
If Not spalte Is Nothing And Not zeile Is Nothing Then
zeile.Copy Range("G1")
Else
MsgBox "nothing found"
End If
End Sub

gruss hary
Anzeige
AW: Two Variables
20.10.2011 16:03:45
Marc
Hi Hary,
this is working great. Thanks. One more thing, is it possible to add that when the value is found ( C7 for example ) it is copying a range ( C20:C25 ) to G1 and not only the C7 value?
Thanks for help.
BR Marc
AW: Two Variables
20.10.2011 18:36:52
hary
Hi Marc
now it looks better.

Private Sub CommandButton1_Click()
Dim spalte As Range 'for find in Columns
Dim zeile As Range 'for find in Rows
Set spalte = Worksheets("Sheet1").Range("C1:E1").Find(What:=Range("A1"), LookIn:=xlValues, _
lookat:=xlWhole)
If Not spalte Is Nothing Then
Set zeile = Worksheets("Sheet1").Columns(spalte.Column).Find(What:=Range("B1"), LookIn:= _
xlValues, lookat:=xlWhole)
If Not zeile Is Nothing Then
Cells(20, zeile.Column).Resize(6, 1).Copy Range("G1")
Else
MsgBox "Cell B1 Value not found"
End If
Else
MsgBox "Cell A1 Value not found"
End If
Application.CutCopyMode = False
End Sub

gruss hary
Anzeige
AW: Two Variables
20.10.2011 22:07:14
Marc
Hi Hary,
works perfectly. Thanks
BR Marc

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige