Understanding a macro code and make changes to it.

samvivi7

New Member
Joined
Feb 19, 2018
Messages
5
Hey All,

I got a macros code made by someone and work okay, not the way I want it to tho. Now that I inherited it I would like to learn how its programmed and be able to make changes to it.
I don't expect anyone to do a line by line explanation but an overview of the program or perhaps a way to understand it on my own would be helpful.

As a start, I would like to change the column QTY, I want a value of 1 (fixed value) in every row. when the new workbook is created.

Thanks for any input,
Sam

Code:
Sub ebtopartslist()
Dim name As String
Dim lastColumn As Integer
Dim lastRow As Long
Dim lastRowcolumn As Long
Application.ScreenUpdating = False


Workbooks.Add
name = "Electricals" & Format(Now(), "ss") & ".xlsx"




ActiveWorkbook.SaveAs (Application.ThisWorkbook.path & "\" & name)


ActiveWorkbook.Sheets(1).Activate


ActiveSheet.Rows("1").RowHeight = 36
ActiveSheet.Rows("4").RowHeight = 10
ActiveSheet.Columns("A").ColumnWidth = 25
ActiveSheet.Columns("B").ColumnWidth = 100
ActiveSheet.Columns("D").ColumnWidth = 25


ActiveSheet.Cells(1, 1).Value = "Company logo"
ActiveSheet.Cells(1, 1).Font.Bold = True
ActiveSheet.Cells(1, 1).Font.Size = 11


ActiveSheet.Cells(1, 2).Value = "Electrical Parts List"
ActiveSheet.Cells(1, 2).Font.Bold = True
ActiveSheet.Cells(1, 2).Font.Size = 24
ActiveSheet.Cells(1, 2).Font.name = "Verdana"


ActiveSheet.Cells(1, 4).Value = "Date:" & Format(Date, "mmmm") & " " & Int(Format(Date, "mm")) & "," & Format(Date, "yyyy")


ActiveSheet.Cells(1, 4).Font.Size = 11
ActiveSheet.Cells(1, 4).Font.name = "Verdana"


ActiveSheet.Cells(2, 1).Value = "Job number:" & vbCrLf & "Machine Modle:" & vbCrLf & "Customer:" & vbCrLf & "Commission:" & vbCrLf


ActiveSheet.Cells(2, 1).Font.Size = 10


ActiveSheet.Cells(2, 1).Font.name = "Verdana"


ActiveSheet.Cells(2, 1).HorizontalAlignment = xlRight


ActiveSheet.Rows("2").AutoFit


ActiveSheet.Cells(2, 2).Value = "Schematic:"
ActiveSheet.Cells(2, 2).HorizontalAlignment = xlCenter
ActiveSheet.Cells(2, 2).VerticalAlignment = xlTop


ActiveSheet.Cells(3, 1).Value = "Part No."


ActiveSheet.Cells(3, 1).Font.Size = 14


ActiveSheet.Cells(3, 1).Font.name = "Verdana"


ActiveSheet.Cells(3, 1).Font.Bold = True


ActiveSheet.Cells(3, 2).Value = "Description"


ActiveSheet.Cells(3, 2).Font.Size = 14


ActiveSheet.Cells(3, 2).Font.name = "Verdana"


ActiveSheet.Cells(3, 2).Font.Bold = True


ActiveSheet.Cells(3, 3).Value = "Qty"


ActiveSheet.Cells(3, 3).Font.Size = 14


ActiveSheet.Cells(3, 3).Font.name = "Verdana"
ActiveSheet.Cells(3, 3).Font.Bold = True


ActiveSheet.Cells(3, 4).Value = "Designation"


ActiveSheet.Cells(3, 4).Font.Size = 14


ActiveSheet.Cells(3, 4).Font.name = "Verdana"
ActiveSheet.Cells(3, 4).Font.Bold = True


Workbooks("fiverrElectronics.xlsm").Activate


ActiveSheet.Cells.Borders.LineStyle = xlLineStyleNone




 desc = Application.WorksheetFunction.Match("Material", Rows("1:1"), 0)
 
 cst = Application.WorksheetFunction.Match("Description", Rows("1:1"), 0)


 partno = Application.WorksheetFunction.Match("Designation", Rows("1:1"), 0)
 
 Manufacture = Application.WorksheetFunction.Match("Manufacturer", Rows("1:1"), 0)
 
Workbooks(name).Sheets(1).Cells.WrapText = True




Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, desc), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, desc).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("A5")
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, cst), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, cst).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("B5")
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, partno), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, partno).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("D5")


lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count


For y = 5 To lastRow


Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text


Next y






lastColumn = Workbooks(name).Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
For i = 5 To lastRow
Workbooks(name).Sheets(1).Rows(i).RowHeight = 43


If i Mod 2 = o Then
For j = 1 To lastColumn


Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)
Next j
End If
Next i
Workbooks(name).Activate
ActiveSheet.Rows("4:4").Select
ActiveWindow.FreezePanes = True


Workbooks(name).Close SaveChanges:=True






Application.ScreenUpdating = True


End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Re: Need Help understanding a macro code and make changes to it.

It would make things easier to explain if you had a link to a sample file so I can try the code and mark up what its doing.
 
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

'Hello Sam
' WELCOME to the site. I congratulate you for wishing to learn a little about programming using VBA (Visual Basic for Applications.)
' I'm a relative newcomer to VBA and Excel myself, but I'll try to explain as much as I can about this program. I'm not sure how much you know
' about VBA. You'll notice I've started these lines with an apostrophe. This turns everything to the right into comments, which VBA ignores.
' You will notice that I have indented some lines of code. This has NO affect on the operation of the code. It just makes it easier to read if
' there is a problem someplace or changes need to be made.
Sub ebtopartslist()
' The name of this macro,(sometimes refereed as a subroutine) This can be called from other macro's or from buttons that the user clicks on.
' The only way to get any of the code here to run, is to call this macro.
Dim name As String
Dim lastColumn As Integer
Dim lastRow As Long
Dim lastRowcolumn As Long
' The DIM lines above tell the computer to set aside space for variables and the type of data each will hold, eg. string (text) or numbers etc.
' Newer versions of Excel ignore 'Integer' which is limited to numbers up to a little over 32,000 and convert that term to 'Long', so usually
' programmers declare their variables as 'Long' to begin with.
Application.ScreenUpdating = False
' This command stops Excel from recalculating every cell every time a change is made to one of them. If you are going from one sheet to
' another, it will stop the changes from being displayed. This can speed up the program as well as stop a lot of screen flickering. It's
' VERY IMPORTANT to remember to turn this feature on at the end of the macro. At that time, Excel does all calculations and screen changes
' at once.
Workbooks.Add
name = "Electricals" & Format(Now(), "ss") & ".xlsx"
' The above two lines create a new workbook by the name of 'Electricals' and gives it the file extension of 'xlsx'. This is assigned to the variable
' of 'NAME'. (Refer to the DIM statement.) It is important to note that a file with this extension CAN NOT have any VBA macros. If you will be
' using VBA, then you must use the extension of '.xlsm'
ActiveWorkbook.SaveAs (Application.ThisWorkbook.Path & "" & name)
' Above command makes this new workbook, the active one, and saves it to your computer as whatever is assigned to the variable of NAME.
ActiveWorkbook.Sheets(1).Activate
' Above command makes sheet #1 the active sheet. Until VBA changes this, everything that happens will be on Sheet #1
' The five lines below set the height of each row and width of columns 'A' & 'B' & 'D'. I have taken the liberty of changing the first two lines
' to demonstrate another way of accomplishing the same task. The computer won't care which is used (what is here or my way) except I
' think you will see how my way will save a lot of typing and shorten the lines of code. I've only used the first two lines as demonstration,
' but the method would apply to any line beginning with the word 'ActiveSheet' that is between my 'WITH ActiveSheet' command and
' my 'End With' command. You can use whichever method you prefer.
' You will notice how I indented the commands. This makes no difference to Excel, just makes it easier to debug if there is a problem. When
' everything is lined up against the left margin, it is hard to read the code and keep track of what is happening and where.
' NOTE: each line following my 'With ActiveSheet' command MUST begin with a period. This tells Excel that '.Rows...' applies to the ActiveSheet
Code:
With ActiveSheet
     .Rows("1").RowHeight = 36
     .Sheet.Rows("4").RowHeight = 10
End With
ActiveSheet.Rows("1").RowHeight = 36
ActiveSheet.Rows("4").RowHeight = 10
ActiveSheet.Columns("A").ColumnWidth = 25
ActiveSheet.Columns("B").ColumnWidth = 100
ActiveSheet.Columns("D").ColumnWidth = 25
ActiveSheet.Cells(1, 1).Value = "Company logo"
ActiveSheet.Cells(1, 1).Font.Bold = True
ActiveSheet.Cells(1, 1).Font.Size = 11
' Where you see the command of .Cells(1,1) that means that whatever is assigned to that cell (value) will be in Row #1 and Column #1 .
' It is always in that order of ROW then COLUMN, so .Cells(1,2) means ROW #1 and COLUMN #2 .
ActiveSheet.Cells(1, 2).Value = "Electrical Parts List"
ActiveSheet.Cells(1, 2).Font.Bold = True
ActiveSheet.Cells(1, 2).Font.Size = 24
ActiveSheet.Cells(1, 2).Font.name = "Verdana"
ActiveSheet.Cells(1, 4).Value = "Date:" & Format(Date, "mmmm") & " " & Int(Format(Date, "mm")) & "," & Format(Date, "yyyy")
' This puts the word 'Date:' in cell(1,4) and formats it as to how you want the date to be displayed. There are several options.
ActiveSheet.Cells(1, 4).Font.Size = 11
ActiveSheet.Cells(1, 4).Font.name = "Verdana"
ActiveSheet.Cells(2, 1).Value = "Job number:" & vbCrLf & "Machine Modle:" & vbCrLf & "Customer:" & vbCrLf & "Commission:" & vbCrLf
' The command of 'vbCrLf' tells Excel to insert a 'line feed' command after each one of the four prompts. All of this will be placed in
' the cell at Row 2, Column 1.
ActiveSheet.Cells(2, 1).Font.Size = 10
ActiveSheet.Cells(2, 1).Font.name = "Verdana"
ActiveSheet.Cells(2, 1).HorizontalAlignment = xlRight
ActiveSheet.Rows("2").AutoFit
' The command '.AutoFit' (again notice the period) means that Excel will automatically adjust the row to the correct size to allow whatever
' has been placed in it, to be fully displayed.
ActiveSheet.Cells(2, 2).Value = "Schematic:"
ActiveSheet.Cells(2, 2).HorizontalAlignment = xlCenter
ActiveSheet.Cells(2, 2).VerticalAlignment = xlTop
ActiveSheet.Cells(3, 1).Value = "Part No."
ActiveSheet.Cells(3, 1).Font.Size = 14
ActiveSheet.Cells(3, 1).Font.name = "Verdana"
ActiveSheet.Cells(3, 1).Font.Bold = True
' The command of 'TRUE' means that the Bold version of the Font is turned ON. To turn it off, use FALSE
ActiveSheet.Cells(3, 2).Value = "Description"
ActiveSheet.Cells(3, 2).Font.Size = 14
ActiveSheet.Cells(3, 2).Font.name = "Verdana"
ActiveSheet.Cells(3, 2).Font.Bold = True
ActiveSheet.Cells(3, 3).Value = "Qty"
ActiveSheet.Cells(3, 3).Font.Size = 14
ActiveSheet.Cells(3, 3).Font.name = "Verdana"
ActiveSheet.Cells(3, 3).Font.Bold = True
ActiveSheet.Cells(3, 4).Value = "Designation"
ActiveSheet.Cells(3, 4).Font.Size = 14
ActiveSheet.Cells(3, 4).Font.name = "Verdana"
ActiveSheet.Cells(3, 4).Font.Bold = True
' At this point if you had used my 'With ActiveSheet' command, you would now insert the 'END WITH' command. As I stated at the beginning,
' every word of 'ActiveSheet' between here and the 'With ActiveSheet', could have been deleted. Just remember to start with the period.
Workbooks("fiverrElectronics.xlsm").Activate
' Another workbook called 'fiverrElectronics.xlsm' will now be the active one. Because this has the file extension of 'xlsm' this workbook can
' contain VBA code.
ActiveSheet.Cells.Borders.LineStyle = xlLineStyleNone
' Means there are no borders around the cells
desc = Application.WorksheetFunction.Match("Material", Rows("1:1"), 0)
' This uses the Excel command of 'MATCH' to look up the position rather than the value of an item within a group. This is then assigned to
' the variable of 'desc' which should have been part of the DIM commands. For more information, highlight the word 'MATCH'
' and press the 'F1' key.
cst = Application.WorksheetFunction.Match("Description", Rows("1:1"), 0)
partno = Application.WorksheetFunction.Match("Designation", Rows("1:1"), 0)
Manufacture = Application.WorksheetFunction.Match("Manufacturer", Rows("1:1"), 0)

Workbooks(name).Sheets(1).Cells.WrapText = True
' The command 'name' refers to the workbook that was created, in this case 'Electricals'. 'Wrap Text' tells Excel to adjust the height of cells to
' hold all the text. For more information and examples, highlight the words 'Wrap Text' and then press the 'F1' key.
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, desc),
' The variable of 'desc' specifies which column will be used. See code a few lines above here to find the value of 'desc'.
Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, desc).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("A5")
' The code of '.Cells(Rows.Count,Desc).End(xlUp))' determines how many rows of data there are in the column specified by 'Desc'.
' Copy all the data from 'fiverrElectronics.xlsm' Sheet #1 , that is in the column described by the variable 'desc' and place it in
' Workbooks("Electricals") starting at 'A5'
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, cst), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, cst).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("B5")
Workbooks("fiverrElectronics.xlsm").Sheets(1).Range(Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(2, partno), Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(Rows.Count, partno).End(xlUp)).Copy Destination:=Workbooks(name).Sheets(1).Range("D5")
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
' This assigns the number of rows of data in this workbook to the variable of 'lastRow' which should have been part of the DIM section.
For y = 5 To lastRow
' This is called a 'For - Next' loop and it tells Excel to repeat any code between here and the 'Next Y' command, the number of times between
' 5 and the number of rows assigned to the variable of 'lastrow' (this was assigned just above here) The variable 'Y' should have been in DIM
' as 'Dim Y as Long'
Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text
' This demonstrates one advantage of putting repeating lines of code withing a loop, because now we can use the variable, in this
' case 'Y', to represent either a Row or a Column. Every time the code loops, the value of 'Y' will increase. The code of
' Cells(Y-3, Manufacture).Text, calculates the needed row number by subtracting '3' from whatever the value of 'Y' is at that moment.
' This DOES NOT affect the real value of 'Y' as far as the loop is concerned.
Next y
' Any time you have a 'For...' loop, you must have a 'Next' command at the end. This increments the variable, in this case 'Y'. If you
' forget to end this loop with the 'Next' command, Excel will give you an error message.
lastColumn = Workbooks(name).Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
' The above two lines calculate the number of Columns and Rows in this workbook and assigns those values to 'lastColumn' & 'lastRow'
' both of which should be part of a DIM statement.
For i = 5 To lastRow
Workbooks(name).Sheets(1).Rows(i).RowHeight = 43
' Again another loop assigning the height of each row from 5 to lastRow to a height of 43.
If i Mod 2 = o Then
' This 'If' statement takes whatever the loop value of 'I' is, divides that by 2 and if the remainder is zero, then do the following.
' If the answer is NOT zero, then skip everything until whatever the code is that follows the 'End If' command.
For j = 1 To lastColumn
' This loop will continue as long as the variable of 'J' is between 1 and the value stored in the variable of lastColumn which was calculate
' a moment ago. When 'J' becomes greater than lastColumn, then stop the loop and continue with the command following 'Next J'.
Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)
' Set the color of the cell at whatever the current loop value of 'I' and 'J' are, to the color specified by the three numbers.
Next j
' Increment the value of 'J' by 1 and return to the 'For J = I to lastColumn' command
End If
' Anytime you have an 'If' loop, you MUST also have an 'End If' command. If you don't, Excel will give you an error message.
Next i
' This increments the value stored in the variable of 'I' and returns command back to the 'For I = 5 To lastRow' command.
Workbooks(name).Activate
' This makes the Workbook that was created, the active workbook. In this case name = Electricals.
ActiveSheet.Rows("4:4").Select
' Using the ActiveSheet, select row #4
ActiveWindow.FreezePanes = True
' Using the ActiveWindow that would have at sometime been named, in this case 'Sheet1' freeze all rows from #1 to #4 . This keeps
' those top rows always visible to the user, no matter how far down they scroll. It is a good idea to do this, because usually the
' top few rows contain column headings, etc. It is frustrating to the user if they have a large sheet of data with many rows and columns,
' but after scrolling down a ways, these headings disappear and the user can't remember what each column meant. The options are
' TRUE or FALSE.
Workbooks(name).Close SaveChanges:=True
' This command saves the workbook specified by the contents of the variable 'Name' and then closes it.
Application.ScreenUpdating = True
' If you remember at the beginning of this macro, there was a command of 'Application.ScreenUpdating = False, and I explained what
' it meant. How it stopped Excel from recalculating each time the user entered something. The 'ScreenUpdating' is now turned on, so
' Excel will now do all the calculations necessary. The user will not see this happen nor will they see the monitor displaying any of the
' different sheets or workbooks that may have been accessed. They will be left looking at whatever the last screen accessed was.
End Sub 'And that, my friend, is, 'THE END'. Every macro (or subroutine) MUST have an 'End Sub' command. If not, Excel will give you
' an error message. You will notice I placed this comment on the same line as the command, rather than on the next line.
' You can place comments any place you want, just so long as there is an apostrophe at the beginning.
'=============== Sample 1 of 2 =============
' What follows IS NOT PART OF THE CODE. I'm using these few lines because I wanted to make a point.
' Now I'd like to add something that I think will demonstrate the benefit of what I said earlier about indenting lines of code. I'll let you decide
' which version would be easier to understand, if there is a problem or changes need to be made. As originally written, the last few lines of
' code were as follows:
For y = 5 To lastRow
Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text
Next y
lastColumn = Workbooks(name).Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
For i = 5 To lastRow
Workbooks(name).Sheets(1).Rows(i).RowHeight = 43
If i Mod 2 = o Then
For j = 1 To lastColumn
Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)
Next j
End If
Next i
Workbooks(name).Activate
ActiveSheet.Rows("4:4").Select
ActiveWindow.FreezePanes = True
Workbooks(name).Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
' Now imagine you have several hundred lines of code all up against the left margin. I think you can see how even with these few lines,
' it is hard to see what is part of one loop and/or part of something else. This is fairly simple code with only one 'For' loop and and one 'IF'
' statement within the original loop. Suppose you had several inside that first 'For' loop?
'=============== Sample 2 of 2 =============
' Here is the way the same code should be displayed. Remember, the computer doesn't care one way or the other.
Code:
For y = 5 To lastRow
    Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & _
    Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text
Next y
    lastColumn = Workbooks(name).Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
    lastRow = Workbooks(name).Sheets(1).UsedRange.Rows.Count
For i = 5 To lastRow
    Workbooks(name).Sheets(1).Rows(i).RowHeight = 43
         If I Mod 2 = o Then
            For j = 1 To lastColumn
                Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)
            Next j
         End If
Next i
Workbooks(name).Activate
ActiveSheet.Rows("4:4").Select
ActiveWindow.FreezePanes = True
Workbooks(name).Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
' Even a couple of spaces indented makes a world of difference because it places the lines of code in sort of blocks that relate to each other.
' Anytime you post a sample of code here, and we always prefer you do if at all possible, because it helps us figure out what is being attempted
' and/or where some error has occured. If you're not sure how to do this, after you post your code, highlight it, then click on the '#' symbol
' near the top of your monitor.
' I hope what I have done here will be of some help to you. If you have any other questions or problems, don't hesitate to post here again. There
' are a great many very talented people here that are more than willing to offer advice or solutions. I'm just starting with Excel, but I can't begin
' to say how much I have learned, simply by asking questions and by studying the answers given to other people. I hope I haven't made to many
' spelling or other errors. It's into the wee hours of the morning and things are starting to get a bit blurry, but I wanted to finish this tonight.

TotallyConfused
 
Last edited:
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

Hello Sam

After I posted posting #4 , I discovered I had made an error. Please change the following code

Code:
With ActiveSheet 
     .Rows("1").RowHeight = 36
     .Sheet.Rows("4").RowHeight = 10
End With

To this code

Code:
With ActiveSheet
     .Rows("1").RowHeight = 36
     .Rows("4").RowHeight = 10
End With

I'm sorry about that.

TotallyConfused
 
Last edited:
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

Hello Sam

After I posted posting #4 , I discovered I had made an error. Please change the following code

Code:
With ActiveSheet 
     .Rows("1").RowHeight = 36
     .Sheet.Rows("4").RowHeight = 10
End With

To this code

Code:
With ActiveSheet
     .Rows("1").RowHeight = 36
     .Rows("4").RowHeight = 10
End With

I'm sorry about that.

TotallyConfused


I can't thank you enough for taking the time out and explaining in such details. I really appreciate it, as this helps me a lot!!!

Thank you..
Thank you..
Thank you..
Thank you..
Thank you..
Thank you..
Thank you..

and again Thank you,
Sam
 
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

I can't thank you enough for taking the time out and explaining in such details. I really appreciate it, as this helps me a lot!!!

Thank you..
Thank you..
Thank you..
Thank you..
Thank you..
Thank you..
Thank you..

and again Thank you,
Sam

Code:
 [COLOR=black][FONT=Calibri]'  Hello Sam[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  WOW! I don't think I have ever received so many 'Thank you's all at once,[/FONT][/COLOR][COLOR=black][FONT=Calibri] in my whole life. You are very welcome. This is the first[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  time I've tried to explain someone's code, and I'll admit, it was fun. I hope that the comments I've given will be of some help to you.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  This has been an interesting project for me, and I wish there was some way we could keep in touch, as I'd like to know how[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  you make out.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  In your positing [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , you stated that you wanted the value of '1' to be placed in each row of the new workbook under the heading of 'QTY'. I'm[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  assuming this is an initial value, and so it WILL NOT change. To make VBA add this value to each row of the new workbook, add any code that[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  I've written in [/FONT][/COLOR][COLOR=red][FONT=Calibri]RED[/FONT][/COLOR][COLOR=black][FONT=Calibri]. What follows is an updated version of certain parts of my posting [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4"]#4[/URL] .[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Sub ebtopartslist()[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim name As String[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim lastColumn As Integer[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim lastRow As Long[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim lastRowcolumn As Long[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' The DIM lines above tell the computer to set aside space for variables and the type[/FONT][/COLOR][COLOR=black][FONT=Calibri] of data each will hold, eg. string (text) or numbers etc. [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  Newer versions of Excel ignore 'Integer' which is limited to numbers up to a little over 32,000 and convert that term to 'Long', so usually [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' programmers declare their variables as 'Long' to begin with.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]Const InitialQTY = 1[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' I've placed this line of code immediately following all the Dim statements as is shown above. [/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' To have a value that CAN NOT be accidently changed by VBA, you declare that variable as a CONST and assign a value to it.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' Because it is being declared here, you will notice there is NO Dim statement. I'm using the variable name of InitialQTY, which is set to the value of '1'.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]'  Further[/FONT][/COLOR][COLOR=red][FONT=Calibri] down, you'll find the following code. [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]For y = 5 To lastRow[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' This is called a 'For Next'[/FONT][/COLOR][COLOR=black][FONT=Calibri] loop and it tells Excel to repeat any code between here and the 'Next Y' command, the number of times between [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  5 and the number of rows assigned to the variable of 'lastRow' (this was assigned just above here) The variable 'Y' should have been in DIM[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  writen as 'Dim Y as Long'[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Workbooks(name).Sheets(1).Cells(y, 2).Value = Workbooks(name).Sheets(1).Cells(y, 2).Value & vbCrLf & Workbooks("fiverrElectronics.xlsm").Sheets(1).Cells(y - 3, Manufacture).Text[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]Workbooks(Name).Sheets(1).Cells(y, 3).Value = InitialQTY[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' To make VBA insert the value assigned to the variable of InitialQTY, insert the above line of code.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  This[/FONT][/COLOR][COLOR=black][FONT=Calibri] demonstrates one advantage of putting repeating lines of code within a loop, because now we can use the variable, in this [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  case 'Y', to represent either a Row or a Column. Every time the code loops, the value of 'Y' will increase. The code of [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  Cells(Y-3, Manufacture).Text, calculates the needed row number by subtracting '3' from whatever the value of 'Y' is at that moment.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  This DOES NOT affect the real value of 'Y' as far as the loop is concerned. [/FONT][/COLOR][COLOR=red][FONT=Calibri]It's used to calculate the position for this one time only.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Next y[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  Any time you[/FONT][/COLOR][COLOR=black][FONT=Calibri] have a 'For...' loop, you must have a 'Next' command at the end. This increments the variable, in this case 'Y'. If you [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  forget to end this loop with the 'Next' command, Excel will give you an error message.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' Now I need to make a correction to something I said in posting [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4"]#4[/URL] . Near the end of the macro, you'll find the following code. [/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]If i Mod 2 = o Then[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  This 'If' statement takes whatever the loop[/FONT][/COLOR][COLOR=black][FONT=Calibri] value of 'I' is, divides that by 2 and if the remainder is zero, then do the following.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  If the answer is NOT zero, then[/FONT][/COLOR][COLOR=black][FONT=Calibri] skip everything until whatever the code is that follows the 'End If' command.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]For j = 1 To lastColumn[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  This loop will continue as long as[/FONT][/COLOR][COLOR=black][FONT=Calibri] the variable of 'J' is between 1 and the value stored in the variable of lastColumn which was calculated[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  a moment ago. When 'J' becomes greater than lastColumn, then stop the loop and continue with the command following 'Next J'.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Workbooks(name).Sheets(1).Cells(i, j).Interior.Color = RGB(192, 192, 192)[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  Set the color of the cell at whatever the current loop value of 'I' and 'J'[/FONT][/COLOR][COLOR=black][FONT=Calibri] are, to the color specified by the three numbers.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Next j[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'[/FONT][/COLOR][COLOR=black][FONT=Calibri]  The above line i[/FONT][/COLOR][COLOR=black][FONT=Calibri]ncrements the value of 'J' by 1 and returns control[/FONT][/COLOR][COLOR=black][FONT=Calibri] to the 'For J = I to lastColumn' command[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End If[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  Anytime you have an 'If' [/FONT][/COLOR][COLOR=red][FONT=Calibri]loop[/FONT][/COLOR][COLOR=black][FONT=Calibri], you MUST also have an 'End If' command. If you don't,[/FONT][/COLOR][COLOR=black][FONT=Calibri] Excel will give you an error message.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' I need to correct the above comment. It should have read, 'Anytime you have an 'If' statement...'. An 'If' is NOT a loop. It's a command[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]' or statement, and is used to allow VBA and Excel to make a decision, and then take appropriate action depending on the decision.[/FONT][/COLOR]
  [COLOR=red][FONT=Calibri]'  I'm sorry about that. I hope my error hasn't caused you any confusion.[/FONT][/COLOR]
  
  [COLOR=black][FONT=Calibri]TotallyConfused[/FONT][/COLOR]
 
Last edited:
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

Hey TotallyConfused,


it's because you took the time to explain in such details.
your comments are really helpful.
My goal is to learn VBA and automate my work.


Example:
I would like to create multiple worksheets or (excel files) from one source file, which are then formatted as needed with a click of a button.
let's say,
One file for Shipping -- where only part numbers and designations show.
Second for ordering --- where the parts are formatted according to the manufacturer and then by part number..and so on.


I have a couple of months until the next project.
let see how far I get.


For now, I will work on understanding and making changes to this code and start learning the basics.


again Thanks,
Sam
 
Upvote 0
Re: Need Help understanding a macro code and make changes to it.

Hello Sam

I'm glad if anything I've said has been helpful to you. This website has been a goldmine of information for me, so I'm happy to help anyone else if I can. You seem interested and determined to learn VBA, and I think that is great. At some future time when you are about ready to pull your hair out because you can't get some line of code to work, I'd like to suggest you take a break and read the response I and Rick Rothstein, a very talented programmer, offered another person that was interested in VBA. This can be found under the heading of 'Education/Knowledge' and is at

https://www.mrexcel.com/forum/lounge-v-2-0/1082674-education-knowledge-background.html


Hopefully, this will encourage you to keep trying until you're successful in getting your code to work. I apologise for the way my initial response is spread out. Somehow when I posted it, while it looked okay originally, it didn't turn out that way.
I'm sure you will figure out how to create those two new sheets from the source sheet. If you have any questions, don't hesitate to post here. Good luck.

TotallyConfused
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

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
Back
Top