Solved: Conditional Copying Of Cell Value

[Bernie]

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]

Bernadette

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

[Bernie]

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]

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]

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]

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]

I think this is what you are looking for


Place this in a Standard Module:
[VBA]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[/VBA]

Then place this in your sheet with the values you are working with (if it is "Sheet1" place this code in the Sheet1 module):
[VBA]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[/VBA]

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

[Bernie]

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]

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]

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

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

[Bernie]

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

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]

I see....I changed it for ya and modified some of the existing code to work with you worksheet better.


Put this in Module1:
[VBA]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
[/VBA]
And then put this in your LabCost module:
[VBA]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
[/VBA]
Let me know what happened

[Bernie]

Mensch Malik,

Incredible! Well it is to me.

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]

Your welcome Bernadette


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

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]

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]

Your welcome

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

[Bernie]

Bonjour Malik,

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

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.

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!!

Merci..... Bernadette

[malik641]

Bonjour Bernadette,

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