Hello, this is my first post on the elektroda forum. I'm just getting started with VBA and have a little problem. The problem to be solved looks like this: In my worksheet, I have such a series in column A. The value of each cell has no meaning. I would like the macro to check if the cell is...

I did not check the operation, but: Error 1: Invalid Rows.Count (x 1 Up, it should be XLUP). Better: Code: text Expand Select all Copy to clipboardFinalRow = Cells(Rows.Count, 1).End(xlUp) Or Code: text Expand Select all Copy to clipboardFinalRow = ActiveSheet.UsedRange.Rows.Count Error 2: For loop. If you assign values to a variable and , this: Code: text Expand Select all Copy to clipboardNext i or (also correctly) the same Next without explicitly naming the variable. Error 3: End For - which is completely unnecessary. Exit from the loop (usually conditional) is done with a command Exit For inside the loop. Not necessary here. Edit: And only now did I look at the body of the loop. Conditions are mutually exclusive. I think that checking should be started from the second line (the first possible hole with 3 non-empty) and we will make an additional condition that the macro does not cause an error if there are less than 3 lines. Code: text Expand Select all Copy to clipboardSub Macro1() FinalRow = ActiveSheet.UsedRange.Rows.Count 'FinalRow = Range(A65536).End(xlUp).Row 'jezeli tylko kolumna If FinalRow

Welcome, Thanks. Macro works. This error with Code: text Expand Select all Copy to clipboardFinalRow = Cells(Rows.Count, 1).End(xlUp) It's a typo - I think I need to start wearing glasses. BTW, do you know any page with detailed understanding of VBA functions? Thanks and regards Peter.

I have the same with - and = Worth recommending: Link1 Link2 Link3 and many others. Greetings.

VBA Macro for Auto-Filling Empty Cells with Above Values in Excel Column

PanPiotrAdamus 39861 3

Treść została przetłumaczona

Zobacz oryginalną wersję tematu

Reply | New topic

#1 6636313 09 Jun 2009 20:30

PanPiotrAdamus PanPiotrAdamus

Level 2
» |

Post #1
6636313 09 Jun 2009 20:30

Hello,

this is my first post on the elektroda forum.

I'm just getting started with VBA and have a little problem.

The problem to be solved looks like this:

Code: text Expand Select all Copy to clipboard
Kolumna Wiersza 1 aaa 2 aaa 3 aaa 4 5 bbb 6 bbb 7 bbb 8 9 c n+1

In my worksheet, I have such a series in column A. The value of each cell has no meaning.
I would like the macro to check if the cell is empty and if it is empty it will copy the previous value to the empty cell.

So far I have written such a macro but it doesn't work (I get error 400).

Code: text Expand Select all Copy to clipboard
Sub Macro () FinalRow = Cells(Rows.Count, 1).End(x1Up).Row i=1 For i=1 To FinalRow If Cells(i,1).Value 0 Then If Cells(i,1).Value=0 Then Cells(i-1,1).Select Selection.Copy ActiveSheet.Paste End If End If Next 1 End For End Sub

Unfortunately, the above code returns an error. Could any of the forum members give me advice on how to solve the above problem?

Thanks !

Peter.
Rust on Embedded Online Conference on 16 July with 33 per cent discount for elektroda users
ADVERTISEMENT
#2 6636630 09 Jun 2009 21:32

adamas_nt adamas_nt

Moderator of Programming
» | Helpful post? (+2)

Post #2
6636630 09 Jun 2009 21:32

I did not check the operation, but:
Error 1: Invalid Rows.Count (x 1 Up, it should be XLUP). Better:
Code: text Expand Select all Copy to clipboard
FinalRow = Cells(Rows.Count, 1).End(xlUp)
Or
Code: text Expand Select all Copy to clipboard
FinalRow = ActiveSheet.UsedRange.Rows.Count

Error 2: For loop. If you assign values to a variable and , this:
Code: text Expand Select all Copy to clipboard
Next i
or (also correctly) the same Next without explicitly naming the variable.
Error 3: End For - which is completely unnecessary. Exit from the loop (usually conditional) is done with a command Exit For inside the loop. Not necessary here.

Edit:
And only now did I look at the body of the loop. Conditions are mutually exclusive. I think that checking should be started from the second line (the first possible "hole" with 3 non-empty) and we will make an additional condition that the macro does not cause an error if there are less than 3 lines.
Code: text Expand Select all Copy to clipboard
Sub Macro1() FinalRow = ActiveSheet.UsedRange.Rows.Count 'FinalRow = Range("A65536").End(xlUp).Row 'jezeli tylko kolumna If FinalRow < 3 Then Exit Sub For i = 2 To FinalRow If Cells(i, 1).Value = "" Then Cells(i, 1) = Cells(i - 1, 1) End If Next End Sub
ADVERTISEMENT
#3 6655963 14 Jun 2009 20:03

PanPiotrAdamus PanPiotrAdamus

Level 2
» | Topic author Helpful post? (+1)

Post #3
6655963 14 Jun 2009 20:03

Welcome,

Thanks. Macro works. This error with
Code: text Expand Select all Copy to clipboard
FinalRow = Cells(Rows.Count, 1).End(xlUp)
It's a typo - I think I need to start wearing glasses.

BTW,

do you know any page with detailed understanding of VBA functions?

Thanks and regards

Peter.
#4 6658202 15 Jun 2009 11:51

adamas_nt adamas_nt

Moderator of Programming

» | Helpful post? (0)

Post #4
6658202 15 Jun 2009 11:51

PanPiotrAdamus wrote:
I think I need to start wearing glasses
I have the same with "-" and "="

Worth recommending:
Link1
Link2
Link3
and many others. Greetings.
Create an account, log in and become active in a forum and ads will not appear. You will receive points by participating in discussions.
Join this discussion.

Install the application

Didn't find an answer? Ask Artificial Intelligence

*I agree to send the question to OpenAI, Anthropic PBC, Perplexity AI, Inc., Kagi Inc., Google LLC - owners of language models in order to prepare the best response. The companies may monitor and log information entered into the form.

*I agree to publicly display my question and answer. The question and answer will be publicly available to everyone. The process may take a few minutes. Upon completion, you will be redirected to the page with the answer.

Wait...(2min)

Reply | New topic

Report a violation of the law

VBA Macro for Auto-Filling Empty Cells with Above Values in Excel Column

Didn't find an answer? Ask Artificial Intelligence