PDA

View Full Version : Solved: Conditional Copying Of Cell Value



Bernie
01-31-2006, 09:12 AM
Hello,

I am new to this site and forum so the zipped word document will I hope explain what I am trying to achieve without repeating myself in this box.

Bernadette

Norie
01-31-2006, 09:22 AM
Bernadette

Try actually posting your question. Some people might be reluctant, or just aren't able to, download attachments.

Bernie
01-31-2006, 09:37 AM
Hello Norie,

Okay I understand why someone may be leery of dowloading some starnge file. So I will give this a whirl below if this is the right place.

Conditional Copying Of A Cell?s Value.



a) Using code, the intent is to copy the numeric value of a cell (R56) and paste it into cell (Q56), if and only if cell (Q56) is left blank?



b) Additionally, need the option to type a number into cell (Q56) regardless of the numeric value found in cell (R56).



c) Logically this means the one may come back to cell (Q56) and change the existing value or delete the value in which case Q56 should now automatically equal R56.



d) The region always starts at line/row 56 and continues downwards, sometimes a few hundred lines.



e) So it is possible that in row 56, Q56 = R56 but in row 58, Q58 will not = R58?. this variance could be found within the region depending on Qxx being left blank or not. The image below might make things clearer.





Ever the optimist? I hope some ?Guru? will be kind enough to provide me a few lines of code to get me started on the right track.




Q

R

Gas

Hemo

Level

Level

0.3561


0.3561


0.5494


0.5494


0.6552


0.5475


0.4437


0.5531


0.5452


0.5452


0.6194


0.6194















Thank you? Bernadette

Bernie
01-31-2006, 09:48 AM
Hello All,

Obviously the diagram I was trying to include did not present iteself properly as being Col Q - titled Gas Level and Col R - titled Hemo Level. With the values underneath.

If someone is able to decipher what it is I am trying to portray that would be excellent and if not well I understand that as well and will graciously move on.

Thank you,

Bernadette

austenr
01-31-2006, 09:54 AM
Hi Bernie,

Could you post a .zip copy of the workbook minus any sensitive data. This would help those trying to help you get a better picture of what you are trying to do. In addition, it would enable them to test their solution on your sample data. To post a .zip copy of the workbook click on the Go Advanced tab below.

malik641
01-31-2006, 09:56 AM
Everything seems doable except I'm confused at this part:


e) So it is possible that in row 56, Q56 = R56 but in row 58, Q58 will not = R58?. this variance could be found within the region depending on Qxx being left blank or not. The image below might make things clearer.
Are you trying to say that Q58 is already filled and because of that you don't want to use R58's value???

malik641
01-31-2006, 10:14 AM
I think this is what you are looking for http://vbaexpress.com/forum/images/smilies/023.gif


Place this in a Standard Module:
Option Explicit

Sub Fill_All_Values()
Dim DataWS As Worksheet
Dim iLastRow As Long
Dim i As Long

Set DataWS = ThisWorkbook.Sheets("Data") 'Change this name to whatever your sheet is called.

With DataWS
iLastRow = .Range("Q" & .Rows.Count).End(xlUp).Row
For i = 56 To iLastRow
If .Range("Q" & i) = "" Then
.Range("Q" & i) = .Range("Q" & i).Offset(0, 1)
End If
Next i
End With
End Sub

Then place this in your sheet with the values you are working with (if it is "Sheet1" place this code in the Sheet1 module):
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
For Each rng In Target
If Left(rng.Address(0, 0), 1) = "Q" Then
If rng.Row > 55 And rng = "" Then rng = rng.Offset(0, 1)
End If
Next rng
End Sub

Use the Fill_All_Values macro to fill all the blanks. After that, the other procedure will handle when you delete values in the "Q" column (anything below row 55, though).

Hope this helps http://vbaexpress.com/forum/images/smilies/001.gif

Bernie
01-31-2006, 02:09 PM
Greetings Austen & Malik,

Okay I have censored the worksheet attached for your scrutiny.

In response to Malik's question:

Are you trying to say that Q58 is already filled and because of that you don't want to use R58's value???

Not exactly. I am trying to arrange (by default) that if no value is typed into cell Qxx then the value of Rxx is picked up and placed automatically into cell Qxx. Alternatively if the user types some value into Qxx then that value is left alone and acted upon by the other cells in the sheet.

Furthermore I ommited to add that I am also trying to do the same thing with
cells Zxx and Yxx respectively.

I fear that this is going to prove to be more difficult than I first made it out to be.

Many thanks for responding to my poorly formulated question.

Bernadette
</FONT></FONT>

Bernie
02-01-2006, 02:11 AM
Hello Malik,

What a mensch and so smart as well.

Your code is just what I needed. Now I will adapt/modify it to work on the other column(s) and presumably tie the macro to a button. Believe the sheet should be good to go.

Merci... Bernadette

malik641
02-01-2006, 05:12 AM
Hello Malik,

What a mensch and so smart as well.

Your code is just what I needed. Now I will adapt/modify it to work on the other column(s) and presumably tie the macro to a button. Believe the sheet should be good to go.

Merci... Bernadette
Why thank you :)

Glad to see you got what you needed. Let us know if you got the other columns working or if you need help on it :thumb

Votre accueil (No I don't speak French, but I thought it would be polite...and fun)

Bernie
02-03-2006, 08:50 AM
Why thank you http://vbaexpress.com/forum/images/smilies/001.gif

Glad to see you got what you needed. Let us know if you got the other columns working or if you need help on it http://vbaexpress.com/forum/images/smilies/023.gif

Votre accueil (No I don't speak French, but I thought it would be polite...and fun)
Hello Malik,

Tried to modify your code to work on the other 2 columns and made a rats nest.

"J'ai besoin de votre aide ici. Grand Temps!!" more succinctly "I need your help, Big Time!".

Merci.. Bernadette

malik641
02-03-2006, 05:05 PM
I see....I changed it for ya and modified some of the existing code to work with you worksheet better. :thumb


Put this in Module1:
Option Explicit

Sub Fill_All_Values()
Application.ScreenUpdating = False
Dim DataWS As Worksheet
Dim iLastRow As Long
Dim i As Long

Set DataWS = ThisWorkbook.Sheets("LabCost") 'Change this name to whatever your sheet is called.

With DataWS
iLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 56 To iLastRow
If .Range("Q" & i) = 0 Then
.Range("Q" & i) = .Range("Q" & i).Offset(0, 1)
End If

If .Range("Y" & i) = 0 Then
.Range("Y" & i) = .Range("Y" & i).Offset(0, 1)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

And then put this in your LabCost module:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
For Each rng In Target
If Left(rng.Address(0, 0), 1) = "Q" Then
If rng.Row > 55 And rng = 0 Then rng = rng.Offset(0, 1)
End If

If Left(rng.Address(0, 0), 1) = "Y" Then
If rng.Row > 55 And rng = 0 Then rng = rng.Offset(0, 1)
End If
Next rng
End Sub

Let me know what happened :)

Bernie
02-06-2006, 03:51 PM
Mensch Malik,

Incredible! Well it is to me. :cool:

After copying your code I stepped through it to watch what it does.

Why do the first columns cells update the new values almost instantaneously; whilst the second columns cells always need the macro to be run for the new values to update?

No reply is needed..... but if you have the time I am curious for an explanation.

Many thanks for your help.

Ayez un jour splendide? Bernadette

malik641
02-06-2006, 08:31 PM
Your welcome Bernadette :thumb


And I'm not sure why it looks that way to you. It should have the same speed and should both update automatically given one of the two conditions:
1.) A value in "Q" and/or "Y" has been deleted (by pressing delete) or the value 0 has been entered.
2.) There is a value in "R" and/or "Z" to be placed into the adjacent "Q" and/or "Y" columns.

The point of the seperate macro was incase you had a ton of blanks in the "Q" and "Y" columns with values in the adjacent "R" and "Z" columns in order to fill up the blanks. The automation works fine in my example, check it out :thumb

Et sentir que libre me demander n'importe quelles questions vous avez.
(Forgive me if it's incorrect....it's courtesy of freetranslation.com....I just wish I knew how to pronounce it :))

Bernie
02-06-2006, 11:40 PM
Hello Malik,

I will attempt to limit my questions and keep you sane.

Well the file you sent had values updating at the speed of light. It's odd why this isn't duplicated in my workbook.

In any case it works fine for me. I really appreciate the time you have spent on this.

Merci... Bernadette

malik641
02-07-2006, 06:08 PM
Your welcome :thumb

And never worry about asking me too many questions. I just want to make sure everything comes clear :thumb So ask away! :yes

Bernie
03-03-2006, 02:07 AM
Bonjour Malik,

J'esp&#232;re que tout est tr&#232;s bien avec vous. :hi:

I changed the layout of the worksheet and adjusted the code you gave me. This worked just fine until :

I used the Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) to automatically add 10 blank lines everytime you double click the mouse.... a lot quicker than manually inserting a new row.

For some reason an error occurs when you double click. The line structure throws a fit and goes on walkabout. :dunno

As you can see I am really in "Begging" mode now. Would you have some time to look at what I did wrong?

The other issue is that the file is now too big (1.67 MB) to upload here. I could send you worksheet images showing Before Error & After Error ...but don't know if that would be enough to work with alongside the code.

Oh Boy, I hope you can help me!! :help

Merci..... Bernadette

malik641
03-03-2006, 12:39 PM
Bonjour Bernadette,

Check your PMs. I sent you my email address so you can send me the file.