Macro to position on a cell?
BPAUD
Posted messages
79
Status
Membre
-
michel_m Posted messages 18903 Registration date Status Contributeur Last intervention -
michel_m Posted messages 18903 Registration date Status Contributeur Last intervention -
Hello,
I am not an expert in either VB or Excel.
However, it seems to me that what I am looking for should not be too "difficult":
On one hand, I have a data sheet, and on the other hand, a more application-oriented sheet.
I can use (VLOOKUP) to bring any value from the row where the header field meets a condition, but I can't seem to position the 'cursor' on the specified cell.
I would like to, through a simple macro, position (Range.Select or something like that) the pointer on the cell of the data sheet in order to make modifications in the row...
Thank you for your help... I have only a few hours left to solve this problem and continue...
I am not an expert in either VB or Excel.
However, it seems to me that what I am looking for should not be too "difficult":
On one hand, I have a data sheet, and on the other hand, a more application-oriented sheet.
I can use (VLOOKUP) to bring any value from the row where the header field meets a condition, but I can't seem to position the 'cursor' on the specified cell.
I would like to, through a simple macro, position (Range.Select or something like that) the pointer on the cell of the data sheet in order to make modifications in the row...
Thank you for your help... I have only a few hours left to solve this problem and continue...
Configuration: Windows XP Internet Explorer 6.0
13 réponses
Hello,
if your cell is fixed
this code selects cell B13
Range("B13").select
if you want to subject it to a conditional
this code checks B13 if it contains toto it selects F13 you can also write different <> "toto"
if Range("B13") = "toto" then
Range("F13").select
end if
See you!
if your cell is fixed
this code selects cell B13
Range("B13").select
if you want to subject it to a conditional
this code checks B13 if it contains toto it selects F13 you can also write different <> "toto"
if Range("B13") = "toto" then
Range("F13").select
end if
See you!
Hello and thank you for your interest.
My problem is as follows:
I am in a sheet let's say "F1"
In this sheet I want to copy one (or more values) and paste them into another sheet called F2 at a specific location which I have the reference for in a known cell of F1.
So in F1
Cell A: Content to copy
Cell B: Address of F2 where I want to "point"
The "macro sought" (or an equivalent Excel function) points to cell X (reference found in F1-Cell B) and pastes (Value) the content of F1 cell A...
Have I been clear... I'm not sure... haven't slept much.
Thanks anyway and see you later.
B
My problem is as follows:
I am in a sheet let's say "F1"
In this sheet I want to copy one (or more values) and paste them into another sheet called F2 at a specific location which I have the reference for in a known cell of F1.
So in F1
Cell A: Content to copy
Cell B: Address of F2 where I want to "point"
The "macro sought" (or an equivalent Excel function) points to cell X (reference found in F1-Cell B) and pastes (Value) the content of F1 cell A...
Have I been clear... I'm not sure... haven't slept much.
Thanks anyway and see you later.
B
Mike :
I'm not sure I understood:
Range("B13").select
does indeed position itself on B13
However, I don't know how to hard-code the reference B13 in my macro.
The reference I want to point to is located in another cell on the sheet
Moreover, the reference is on a different sheet from the one where I want to point.
I'm not sure I understood:
Range("B13").select
does indeed position itself on B13
However, I don't know how to hard-code the reference B13 in my macro.
The reference I want to point to is located in another cell on the sheet
Moreover, the reference is on a different sheet from the one where I want to point.
Hi, hello Mike,
I see roughly what you want to do: if the key reference exists you modify fields, otherwise it's a new record?
If that's what you're looking for, in both cases it's a row search...
Well, the best is for you to attach an excerpt of your workbook without any confidential data
for that
www.cjoint.com
possibly, you compress it with .zip (not .rar)
I see roughly what you want to do: if the key reference exists you modify fields, otherwise it's a new record?
If that's what you're looking for, in both cases it's a row search...
Well, the best is for you to attach an excerpt of your workbook without any confidential data
for that
www.cjoint.com
possibly, you compress it with .zip (not .rar)
Hello,
here is an example to guide you:
http://www.cijoint.fr/cjlink.php?file=cj200805/cijplB6uv2.xls
eric
here is an example to guide you:
http://www.cijoint.fr/cjlink.php?file=cj200805/cijplB6uv2.xls
eric
http://www.cijoint.fr/cjlink.php?file=cj200805/cijrP51M55.xls
I persist in trying...
But your silence worries me....
I must have made some missteps... quite unintentionally, I assure you! which must have irritated all three of you.
In any case, and even if I have worn out your patience too much, thank you...
I will try to return to this forum one day when I have less stress and urgency to see more clearly and familiarize myself with the structures and modalities.
A thousand apologies and THANK YOU anyway... I will try to continue searching but I really think I have exhausted my available resources .... I will let it go and truncate my solution .... Too bad this time....
But the question remains unanswered... it seems to me that the answer should be childishly easy.... and that it should be staring me in the face. It must be the pressure.
Best regards
I persist in trying...
But your silence worries me....
I must have made some missteps... quite unintentionally, I assure you! which must have irritated all three of you.
In any case, and even if I have worn out your patience too much, thank you...
I will try to return to this forum one day when I have less stress and urgency to see more clearly and familiarize myself with the structures and modalities.
A thousand apologies and THANK YOU anyway... I will try to continue searching but I really think I have exhausted my available resources .... I will let it go and truncate my solution .... Too bad this time....
But the question remains unanswered... it seems to me that the answer should be childishly easy.... and that it should be staring me in the face. It must be the pressure.
Best regards
I must have made a few blunders... quite unintentionally, I assure you! They must have irritated all three of you.
But no, not at all. Don't worry, if everyone was like you, it would be just fine ;-)
That being said, the solution proposed by michel_m at 5:52 PM works very well and is suited to your workbook (which I didn't have when I made my response).
Test it on his workbook and you'll see that it's okay. Right-click on the "Sheet2" tab and 'view code' to see. And you also have a procedure in module 1
eric
But no, not at all. Don't worry, if everyone was like you, it would be just fine ;-)
That being said, the solution proposed by michel_m at 5:52 PM works very well and is suited to your workbook (which I didn't have when I made my response).
Test it on his workbook and you'll see that it's okay. Right-click on the "Sheet2" tab and 'view code' to see. And you also have a procedure in module 1
eric
Yes, you have .offset(row, column) which recalculates your reference.
If row = 0: same line, if row > 0: move down by row lines, if row < 0: move up.
If column = 0: same column, column > 0: to the right, column < 0: to the left.
Of course, the offset must keep within the limits of the sheet.
Example:
Range("B2").offset(1,2).select selects cell D3
or
ActiveCell.offset(0,3).value = "toto" puts toto in the 3rd cell to the right of the active cell, same line.
Eric
If row = 0: same line, if row > 0: move down by row lines, if row < 0: move up.
If column = 0: same column, column > 0: to the right, column < 0: to the left.
Of course, the offset must keep within the limits of the sheet.
Example:
Range("B2").offset(1,2).select selects cell D3
or
ActiveCell.offset(0,3).value = "toto" puts toto in the 3rd cell to the right of the active cell, same line.
Eric
Hi BPAUD,
If you need help, follow your discussion, and as our friend michel_m asked, post an example of your problem on the forum.
https://www.cjoint.com/
Three of us members are offering you our help, including eriic and michel_m who are very active and appreciated on the forum, whom I greet in passing. So make an effort or mark your status as resolved if you no longer need us, with a little thank you along the way, it’s always appreciated.
See you later!
If you need help, follow your discussion, and as our friend michel_m asked, post an example of your problem on the forum.
https://www.cjoint.com/
Three of us members are offering you our help, including eriic and michel_m who are very active and appreciated on the forum, whom I greet in passing. So make an effort or mark your status as resolved if you no longer need us, with a little thank you along the way, it’s always appreciated.
See you later!
Hello again,
I never let anyone down and I appreciate your help!!!
But either I don't understand, or I didn't explain myself well.... in any case, it still doesn't work.
I did indeed have to be away for a few hours, but before that, I sent (or at least tried to send) a little example....
Thank you for your help, I really need it!
B.
I never let anyone down and I appreciate your help!!!
But either I don't understand, or I didn't explain myself well.... in any case, it still doesn't work.
I did indeed have to be away for a few hours, but before that, I sent (or at least tried to send) a little example....
Thank you for your help, I really need it!
B.
OK, BPAUD to try to help you, but close the other requests posted in other sections of this forum:
it's pointless;
not elegant (you don't trust the people in this section?);
it reduces the effectiveness of the help...
Michel
it's pointless;
not elegant (you don't trust the people in this section?);
it reduces the effectiveness of the help...
Michel
It seems I don't understand how the distribution of topics works...
I'm trying not to pollute, but apparently, I've failed.
I will try to improve.
I attempted to resend the requested file
https://www.cjoint.com/?flq3Y2JORC
I'm renewing it here.
SORRY!
I'm trying my best....
I'm trying not to pollute, but apparently, I've failed.
I will try to improve.
I attempted to resend the requested file
https://www.cjoint.com/?flq3Y2JORC
I'm renewing it here.
SORRY!
I'm trying my best....
attached proposal
https://www.cjoint.com/?flrYrfh8UA
to view the main code, right-click on the sheet 2 tab and view the code
validation of changes or new ref in module1 of VBE
Michel
Edit:
1/ delete the formulas in sheet1!
2/ improve the validation of a creation or modification
Sub validate()
Sheets(1).Cells(row, 2) = Range("C7")
Sheets(1).Cells(row, 3) = Range("D7")
Sheets(1).Cells(row, 4) = Range("E7")
Sheets(1).Cells(row, 5) = Range("F7")
MsgBox " update completed in the database"
End Sub
Mark in the programming forum that your issue discussion is taking place in the office forum!
https://www.cjoint.com/?flrYrfh8UA
to view the main code, right-click on the sheet 2 tab and view the code
validation of changes or new ref in module1 of VBE
Michel
Edit:
1/ delete the formulas in sheet1!
2/ improve the validation of a creation or modification
Sub validate()
Sheets(1).Cells(row, 2) = Range("C7")
Sheets(1).Cells(row, 3) = Range("D7")
Sheets(1).Cells(row, 4) = Range("E7")
Sheets(1).Cells(row, 5) = Range("F7")
MsgBox " update completed in the database"
End Sub
Mark in the programming forum that your issue discussion is taking place in the office forum!
Good evening Michel,
I don't know if you remember me...
Some time ago, I had several issues...
Including enriching a database from an input screen which you had resolved, as I recall.
Since then, I’ve been trying to solve my other problems and I thought I was getting close...
I'm going back to your example to put it into practice and, unfortunately:
1- it seems that despite your statement: ""lig = .Range("B4:B1000").Find(Target).Row"", I can’t enter a record beyond 9
2- I’m trying to understand how to adapt, specifically to add additional fields to the input, and it’s not working (I have added Range("G7")=... to the code while trying to respect the syntax that you applied...)
Range("D7") = .Cells(lig, 3)
Range("E7") = .Cells(lig, 4)
Range("F7") = .Cells(lig, 5)
Range("G7") = .Cells(lig, 6)
I tried to re-upload the file:
http://www.cijoint.fr/cjlink.php?file=cj200806/cijfU3AIKM.xls
I’m lost!
Thank you
I don't know if you remember me...
Some time ago, I had several issues...
Including enriching a database from an input screen which you had resolved, as I recall.
Since then, I’ve been trying to solve my other problems and I thought I was getting close...
I'm going back to your example to put it into practice and, unfortunately:
1- it seems that despite your statement: ""lig = .Range("B4:B1000").Find(Target).Row"", I can’t enter a record beyond 9
2- I’m trying to understand how to adapt, specifically to add additional fields to the input, and it’s not working (I have added Range("G7")=... to the code while trying to respect the syntax that you applied...)
Range("D7") = .Cells(lig, 3)
Range("E7") = .Cells(lig, 4)
Range("F7") = .Cells(lig, 5)
Range("G7") = .Cells(lig, 6)
I tried to re-upload the file:
http://www.cijoint.fr/cjlink.php?file=cj200806/cijfU3AIKM.xls
I’m lost!
Thank you
Hi,
I had fun creating some little macros for you to help you understand them.
Of course, they can be written differently and especially more concisely, but it's a good approach.
If you place your search area on the same sheet, here’s what it might look like.
Right-click on the sheet1 tab to see the code associated with the button and in the VBA the macros with explanations for each line after the apostrophe '
https://www.cjoint.com/?flxrMpJIoJ
See you!
_________________________________________________________________________
You were looking for help. The forum members were there for you, voluntarily. Please be kind enough to let us know if your problem is resolved. In order to classify the
Request.
I had fun creating some little macros for you to help you understand them.
Of course, they can be written differently and especially more concisely, but it's a good approach.
If you place your search area on the same sheet, here’s what it might look like.
Right-click on the sheet1 tab to see the code associated with the button and in the VBA the macros with explanations for each line after the apostrophe '
https://www.cjoint.com/?flxrMpJIoJ
See you!
_________________________________________________________________________
You were looking for help. The forum members were there for you, voluntarily. Please be kind enough to let us know if your problem is resolved. In order to classify the
Request.
https://www.cjoint.com/?fnaWqqSXJX
Good evening Eric,
it seems that the cell content offset isn't working...
I tried breaking it down into successive phases to try to understand... but ???
Bernard
Good evening Eric,
it seems that the cell content offset isn't working...
I tried breaking it down into successive phases to try to understand... but ???
Bernard
Hello,
because you tried incorrectly....
It’s the last line of your macro that needs to be corrected.
On the other hand, you started to clean up the unnecessary lines (scrolling...) generated by the macro recorder.
That's good, but you need to go even further.
The recorder randomly adds unnecessary .select statements that bloat the code, make it difficult to read, and also considerably slow it down.
Furthermore, the copy/paste generated by the macro can be advantageously replaced by directly setting the cell value.
I’ve added 2 examples that do exactly the same thing as your code.
The second one does not bring back the offset values in sheet1, which is unnecessary unless you think it’s important to be visible here.
http://www.cijoint.fr/cjlink.php?file=cj200805/cijwgYBKjp.xls
Good continuation and don’t get discouraged, even after 5 years you will still be learning about Excel.
Eric
because you tried incorrectly....
It’s the last line of your macro that needs to be corrected.
On the other hand, you started to clean up the unnecessary lines (scrolling...) generated by the macro recorder.
That's good, but you need to go even further.
The recorder randomly adds unnecessary .select statements that bloat the code, make it difficult to read, and also considerably slow it down.
Furthermore, the copy/paste generated by the macro can be advantageously replaced by directly setting the cell value.
I’ve added 2 examples that do exactly the same thing as your code.
The second one does not bring back the offset values in sheet1, which is unnecessary unless you think it’s important to be visible here.
http://www.cijoint.fr/cjlink.php?file=cj200805/cijwgYBKjp.xls
Good continuation and don’t get discouraged, even after 5 years you will still be learning about Excel.
Eric
Eric
Thank you for your help!
Your "test2" is great!
Now ... question c...
How do I create a button in my 'real sheet' and link a macro inspired by your suggestion?
Sorry ... I have to start from scratch!!! (;-)))))
Talk to you later, I'm going to mow the lawn and then I'll be back ... they say it’s going to rain this afternoon!
Thank you for your help!
Your "test2" is great!
Now ... question c...
How do I create a button in my 'real sheet' and link a macro inspired by your suggestion?
Sorry ... I have to start from scratch!!! (;-)))))
Talk to you later, I'm going to mow the lawn and then I'll be back ... they say it’s going to rain this afternoon!
You write your macro then go to 'View / Toolbars... / Forms'.
Click on the 'Button' control,
draw it on your sheet by clicking and dragging,
assign the button to your macro, confirm
and edit the text on the button.
Click on the 'Button' control,
draw it on your sheet by clicking and dragging,
assign the button to your macro, confirm
and edit the text on the button.
Eric,
Sorry...
I almost forgot....
I have three other departure issues before I can play...
PB 1
Is it possible to bypass the apparent impossibility of validating a data entry (choosing from a table) listed outside the active sheet?
I thought I was doing well and grouped all my tables in a single tab (a sheet), which allows me to ensure simple and centralized maintenance, but the aforementioned limitation forces me to copy (with reference, of course) all the tables I need into the 'work' sheet...
PB2
I often use the 'VLOOKUP' and 'HLOOKUP' functions which are so practical for bringing information from one sheet to another... However, there seems to be a limit as I can only retrieve one piece of data!
Let’s say in a sheet2 I want to retrieve information regarding a lookup key
So, for example, I want to bring back THE LIST of cars from AAA-Pierre.
VLOOKUP points to the first occurrence found in the lookup area and returns the info from that row... That's it
I know that interactively the auto filter gives answers, but that's not the point...
PB3
I use criteria in tables that are sometimes linked in 'lineage'. We could say that there are lists and sublists.
How can I ensure that when I select the level 1 criterion (in this example, the car brand), selecting the level 2 criterion (model) only presents me with valid choices related by lineage...?
Yeah... maybe that's a lot of topics in one message...
I guess that's not in the charter, right....
What should I do?
Should I open three new topics?
Or... is this okay as it is?
Attached is the example file...
http://www.cijoint.fr/cjlink.php?file=cj200805/cijpS7j9ES.xls
Best regards
Bernard
Sorry...
I almost forgot....
I have three other departure issues before I can play...
PB 1
Is it possible to bypass the apparent impossibility of validating a data entry (choosing from a table) listed outside the active sheet?
I thought I was doing well and grouped all my tables in a single tab (a sheet), which allows me to ensure simple and centralized maintenance, but the aforementioned limitation forces me to copy (with reference, of course) all the tables I need into the 'work' sheet...
PB2
I often use the 'VLOOKUP' and 'HLOOKUP' functions which are so practical for bringing information from one sheet to another... However, there seems to be a limit as I can only retrieve one piece of data!
Let’s say in a sheet2 I want to retrieve information regarding a lookup key
So, for example, I want to bring back THE LIST of cars from AAA-Pierre.
VLOOKUP points to the first occurrence found in the lookup area and returns the info from that row... That's it
I know that interactively the auto filter gives answers, but that's not the point...
PB3
I use criteria in tables that are sometimes linked in 'lineage'. We could say that there are lists and sublists.
How can I ensure that when I select the level 1 criterion (in this example, the car brand), selecting the level 2 criterion (model) only presents me with valid choices related by lineage...?
Yeah... maybe that's a lot of topics in one message...
I guess that's not in the charter, right....
What should I do?
Should I open three new topics?
Or... is this okay as it is?
Attached is the example file...
http://www.cijoint.fr/cjlink.php?file=cj200805/cijpS7j9ES.xls
Best regards
Bernard
Hello,
PB3
I use criteria in a table that are sometimes linked in 'lineage'; we could say that there are lists and sublists.
How can I ensure that when I select the level 1 criterion (in this example, the Car Brand), the selection of the level 2 criterion (model) only presents me with valid choices in lineage...?
The group of cells (level 2) must be named after the brand (level 1).
For example:
select the brands and then name the group "brand"
select the models and then name the group "mercedes" (the spelling must match exactly that of the group "brand".
In cell A1, "data validation" "list" =brand
in B1 "data validation" "list" =INDIRECT(A1)
PB3
I use criteria in a table that are sometimes linked in 'lineage'; we could say that there are lists and sublists.
How can I ensure that when I select the level 1 criterion (in this example, the Car Brand), the selection of the level 2 criterion (model) only presents me with valid choices in lineage...?
The group of cells (level 2) must be named after the brand (level 1).
For example:
select the brands and then name the group "brand"
select the models and then name the group "mercedes" (the spelling must match exactly that of the group "brand".
In cell A1, "data validation" "list" =brand
in B1 "data validation" "list" =INDIRECT(A1)
Hello Chtilou,
Received but not satisfactory:
At the time of writing, the "brands" are not yet known, nor are the "models," by the way.
This information is provided by the user at initialization... and maintained (expanded for example) over time.
That's why I'm creating the "Brands" - "Models" link sheet which is also enriched over time.
I think that the solution, if it exists, will probably rely on this table... more than on the basic table zones....
But I will look at your suggestion with interest and specifically delve into the "indirect" side.
Thanks anyway
Bernard
Received but not satisfactory:
At the time of writing, the "brands" are not yet known, nor are the "models," by the way.
This information is provided by the user at initialization... and maintained (expanded for example) over time.
That's why I'm creating the "Brands" - "Models" link sheet which is also enriched over time.
I think that the solution, if it exists, will probably rely on this table... more than on the basic table zones....
But I will look at your suggestion with interest and specifically delve into the "indirect" side.
Thanks anyway
Bernard
I'll try to be clear, but I only slept two hours, so....
Let's say I have a sheet that we'll call BdD which contains records.
On another sheet, I have a consultation screen and a way to enter new records.
It works: I create, I consult, I pull back all the detailed data for consultation, etc.
WHERE IT GETS TRICKY is if I want to MODIFY or complete a record...
If I apply my recording procedure, I create a NEW record and thus a duplicate....
I told myself "Easy," I know the key elements of the record to modify... a VLOOKUP that can bring me back the 'content' of the row, there must be a way to "point" to the header field of the row in the database... and then paste the content from the input screen (to keep it simple) with the modified data...
But here I can't find how.
Thank you for your interest and help... I committed to write something for my daughter who is visiting for the weekend ... and she will leave "without me..." that’s not very comfortable.
B.