PDA

View Full Version : Help with the meaning of this code



moiishchiu
05-12-2016, 10:59 PM
Cells(1, DELTA).Select
ActiveCell.EntireColumn.Insert
Cells(1, DELTA + 1).Select
ActiveCell.EntireColumn.Insert
DELTA = DELTA + 2
Columns(DELTA - 2).ColumnWidth = 5

mancubus
05-12-2016, 11:55 PM
it is a 'modified' recorded macro to insert 2 blank columns starting from the column which is given by the variable DELTA and to set column width of that (+2-2 = 0) column.

procedure would be like:



Cells(1, DELTA).Resize(, 2).EntireColumn.Insert
Columns(DELTA).ColumnWidth = 5

moiishchiu
05-13-2016, 01:02 AM
Also, what does this code mean as well especially the "=sum(R43C:R47C)"

Cells(48, DELTA - 2).FormulaR1C1 = "=sum(R43C:R47C)"

PAB
05-13-2016, 02:30 AM
Hi moiishchiu,


What does this code mean, especially the "=sum(R43C:R47C)"
Cells(48, DELTA - 2).FormulaR1C1 = "=sum(R43C:R47C)"

It means...

Cells(ROW 48, COLUMN which is set by the variable DELTA).FormulaR1C1 = "SUM(RC43:RC47)"

Basically, the formula means SUM COLUMN RC ROW 43 to COLUMN RC ROW 47.

I hope this helps!

moiishchiu
05-15-2016, 10:58 PM
Thanks it helped a lot !

moiishchiu
05-15-2016, 11:03 PM
I'm a newbie and I was tasked to improve on this current program in excel, I need all the help I can get, what does this code means too haha :crying:

InvoiceDate = Sheets("tracking").Rows(1).Find(What:=strSearch, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Column

p45cal
05-16-2016, 04:09 AM
It looks in row 1 of the sheet called "tracking" for whatever's in strSearch and comes back with the column number that it finds it in.

Aflatoon
05-16-2016, 04:46 AM
Basically, the formula means SUM COLUMN RC ROW 43 to COLUMN RC ROW 47.


Actually, it's sum the current row, from columns 43 to 47. It's an R1C1 formula, not A1.

p45cal
05-16-2016, 05:04 AM
Actually, it's sum the current row, from columns 43 to 47. It's an R1C1 formula, not A1.!!??
Do check this by putting it into a sub!

Aflatoon
05-16-2016, 05:44 AM
Apologies - my comment was based on the code in PAB's post, which says:

Cells(ROW 48, COLUMN which is set by the variable DELTA).FormulaR1C1 = "SUM(RC43:RC47)"

The original code actually refers of course to rows 43 to 47 of the current column.

moiishchiu
05-16-2016, 06:00 PM
It looks in row 1 of the sheet called "tracking" for whatever's in strSearch and comes back with the column number that it finds it in.

Thanks !

PAB
05-17-2016, 10:01 AM
Glad we could help, thanks for the feedback.

moiishchiu
05-18-2016, 07:50 PM
Hi, im just experimenting now, so why does my below code not work, I want it to add up all the numbers in column 7. When I debugged it it says type mismatch on this line TotalSum = TotalSum + Cells(counter2, 7)

Sub TotalSumFile()
Dim TotalSum As Integer
Dim counter2 As Integer
Dim lengthoflist As Long

lengthoflist = Sheets("RawData").UsedRange.Rows.Count
For counter2 = 1 To lengthoflist
If Cells(counter2, 7) <> 0 Then
TotalSum = TotalSum + Cells(counter2, 7)
End If
Range("m15") = TotalSum
Next counter2
End Sub

p45cal
05-19-2016, 07:28 AM
Whatever's in Cells(counter2, 7) is not a whole number (it could be text, or a number with decimals?)