Hello, everyone,
I was able to figure this macro out almost completly. However, I'm missing a piece which I never dealt with before.
Basically, I have one excel file which I update monthly and I need to get the figures from a colleague's workbook. However, the code below doesn't compute lr2 properly because there are formulas afterwards, although there is no value in it.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Sept[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]
I would like the sub to ignore the cells with formulas and return me the number of the column from July, in this case. Is there any way to do this?
For reference, in the sub I posted above, it returns me 62 when it should return 56
Thanks for your help,
ORoxo
I was able to figure this macro out almost completly. However, I'm missing a piece which I never dealt with before.
Basically, I have one excel file which I update monthly and I need to get the figures from a colleague's workbook. However, the code below doesn't compute lr2 properly because there are formulas afterwards, although there is no value in it.
Code:
Sub GetCarlaValues()Dim sourcefile As Workbook, Dados As Worksheet, lc As Integer, lc2 As Integer, lr As Integer
lr = KRIs.Cells(Rows.Count, 2).End(xlUp).Row
lc = KRIs.Cells(85, Columns.Count).End(xlToLeft).Column
Set sourcefile = Workbooks.Open("path\workbook1.xlsx")
lr2 = ActiveWorkbook.Worksheets("Dados").Cells(516, Columns.Count).End(xlToLeft).Column
KRIs.Cells(85, lc) = Dados.Cells(516, lc2)
KRIs.Cells(86, lc) = Dados.Cells(510, lc2)
KRIs.Cells(87, lc) = Dados.Cells(515, lc2)
KRIs.Cells(152, lc) = Dados.Cells(594, lc2)
KRIs.Cells(153, lc) = Dados.Cells(587, lc2)
KRIs.Cells(154, lc) = Dados.Cells(590, lc2)
KRIs.Cells(167, lc) = Dados.Cells(554, lc2)
KRIs.Cells(168, lc) = Dados.Cells(557, lc2)
KRIs.Cells(169, lc) = Dados.Cells(552, lc2)
End Sub
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Sept[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]
I would like the sub to ignore the cells with formulas and return me the number of the column from July, in this case. Is there any way to do this?
For reference, in the sub I posted above, it returns me 62 when it should return 56
Thanks for your help,
ORoxo