Please Check my VBA code

Meko1

New Member
Joined
Oct 20, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hello
I would like a advanced VBA user to see my code pls.

I am just making simple if functions in VBA, but since I am new it is not that easy to me.

So the code is following:


If Sheet10.Range("N2").Value > 0 Then ActiveSheet.Range("B22").Value = 0.5
Else: Range("B22").Value = 0

If Sheet10.Range("M2").Value > 0 Then ActiveSheet.Range("B23").Value = 0.1
Else: Range("B23").Value = 0

If Sheet10.Range("L2").Value > 0 Then ActiveSheet.Range("B25").Value = 0.1
Else: Range("B25").Value = 0

If Sheet10.Range("J2").Value > 0 Then ActiveSheet.Range("B24").Value = 0.1
Else: Range("B24").Value = 0

If Sheet10.Range("K2").Value > 0 Then ActiveSheet.Range("B24").Value = 0.1
Else: Range("B24").Value = 0


So, am I correct, and while using else I want it to leave that cell with value 0.

And I have another issue, in other problem, while moving data from one sheet cells to another sheet cells, and those cells already have data inside, how to ask VBA if there is data go 1cell down and fill from there.

Tanks in advance.
Hope for your quick response.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Joined
Oct 9, 2018
Messages
49
Hi Meko,

I would change it to:
VBA Code:
With  ActiveSheet

If Sheet10.Range("N2").Value > 0 Then .Range("B22").Value = 0.5
Else: .Range("B22").Value = 0

If Sheet10.Range("M2").Value > 0 Then .Range("B23").Value = 0.1
Else: .Range("B23").Value = 0

If Sheet10.Range("L2").Value > 0 Then .Range("B25").Value = 0.1
Else: .Range("B25").Value = 0

If Sheet10.Range("J2").Value > 0 Then .Range("B24").Value = 0.1
Else: .Range("B24").Value = 0

If Sheet10.Range("K2").Value > 0 Then .Range("B24").Value = 0.1
Else: .Range("B24").Value = 0

End with
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
My first and foremost advice to anyone starting out in VBA is don't use ActiveSheet, Select, ActiveWorkbook etc., unless you have a really good reason for it. It's bound to bite you in the *** sooner or later. For more details see e.g. here.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
I see your question and would like to know your overall objective.
It's much easier to help new users if we know your overall objective.
Having a half dozen if statements is not normally needed if we know the over all objective
And see you may even have a lot more of these and again it would be nice to know the end game.

Some times we can build a simple loop to do something like this if we knew the end result.
And I agree using active sheet is not a good plan.

I use things sort of like this
Is Sheets("Alpha").Range("A1").Value="Duck" then Sheets("Bravo").Range("A1").value="Bird"

And to find the next empty cells is easy if we know what column your sending your new data to.
I look forward to seeing back from you.
And else would mean do this. If you want to do nothing you do not need to use else.
 

Meko1

New Member
Joined
Oct 20, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hey,
Thank you all for the information, and I agree that using active sheet is not the best idea, but, in my case I am making a school excel workbook where I will have around 500 sheets, for each student and even more if I need them, so it is just impossible to use 500 different names for the macro, since I am using the same macro for every new sheet. That is why ActiveSheet works best atm for me.

Regarding finding new empty cells. So far this is code which I am using and as I mentioned I will have few hundreds of sheets so, the data needs to be filled from every ActiveSheet to Sheet1. As you can see first data transfer includes cells B4, C4, D4, E4. If I need some extra cells I will add them, now I am stuck with that I do not know how to ask VBA if they see data in those cells to go 1 cell down and fill those cells in the case B5,C5,D5,E5...

Sheet1.Range("B4") = ActiveSheet.Range("B6")
Sheet1.Range("C4") = ActiveSheet.Range("B7")
Sheet1.Range("D4") = ActiveSheet.Range("B5")
Sheet1.Range("E4") = ActiveSheet.Range("B12")


At the moment I am improving the new excel file for the school, I want them to work in an improved workbook, since the old one is mess, and wile renewing it, it will take hours, days to transfer all the necessary data from one workbook to another. So my solution is that using several macros, buttons, to cut the time at its maximum.
This is last problem I have in VBA and I am sure after it the file will be ready.

Thanks in advance,
Hope to hear from you soon.
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
The obvious solution if you need to work on several sheets which look similar would be something like

VBA Code:
dim ws as worksheet

for each ws in thisworkbook.worksheets
  if instr(1, ws.name, "sheet", vbtextcompare) > 0 then ' edit to match whatever criteria the sheets you want to copy from should fulfill
     Sheet1.Range("B" & Application.WorksheetFunction.Max(3, Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row)).Offset(1, 0)= ws.range("B6")
     etc.
  end if
next ws

You could also use an offset for each iteration of the loop to increase the offset by 1
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I hope you understand I'm just trying to help you and not disagree with the way you think you need to do this:

Creating a Button in 500 sheets and then expecting user to click each button is not the best way.

We can write one script that will run for all sheets and return the end result back to a master sheet
And this same script could run on any new sheets you may add.

So tell me. Is the script to look for the same cells in all sheets except for sheet named master?
The sheet named Master would be the sheet where all the values are returned to.
And we could even show you the name of the sheet when the returned value are from.

If something like this would work all you need if for one script and one button on the master sheet

Are you interested in this way?
And then if you want to modify the script you only need to modify one script
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
Here is a script which should do all you want with no need for a button on every sheet.
Put this script in a button on your master sheet which I have named Master

The script will perform what you want on all sheets even new sheets.

VBA Code:
Sub Check_All_Sheets()
'Modified 12/7/2020  9:39:01 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrowa As Long
Dim Lastrowb As Long
Dim Lastrowc As Long
Dim Lastrowd As Long
Dim Lastrowe As Long

For i = 2 To Sheets.Count
    Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Lastrowb = Sheets("Master").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Lastrowc = Sheets("Master").Cells(Rows.Count, "C").End(xlUp).Row + 1
    Lastrowd = Sheets("Master").Cells(Rows.Count, "D").End(xlUp).Row + 1
    Lastrowe = Sheets("Master").Cells(Rows.Count, "E").End(xlUp).Row + 1
    
    With Sheets(i)
        .Range("B6").Copy Sheets("Master").Cells(Lastrowb, 2)
        .Range("B7").Copy Sheets("Master").Cells(Lastrowc, 3)
        .Range("B5").Copy Sheets("Master").Cells(Lastrowd, 4)
        .Range("B12").Copy Sheets("Master").Cells(Lastrowe, 5)
        Sheets("Master").Cells(Lastrowa, 1).Value = .Name
    End With
Next
Application.ScreenUpdating = True
End Sub
 
Solution

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
@My Aswer Is This Your script will crash unless the first indexed sheet is the master one. Considering the number of sheets the OP indicates is involved you may want to turn off more than just screenupdating, these are the two macros I usually use for that...

VBA Code:
Sub deactivate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    ' ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
End Sub

Sub reactivate()
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
OK. I'm assuming the Master sheet is sheet 1
If that is not true let me know.

You did say:
Sheet1.Range("B4") = ActiveSheet.Range("B6")

Assume this means first sheet in workbook
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,910
Members
418,250
Latest member
Jebacmakro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top