Range Cell

zipatei · Nov 1, 2017

I need help.
How to put cell wi.Sheets("Data Operação").Range("Q5") in the cell ThisWorkbook.Sheets.Range("P" & rngRow).

If i add, the result equal "" but expected is 100:

wi.Sheets("Data Operação").Range("Q5").Value = ThisWorkbook.Sheets.Range("P" & rngRow).Value

My code

Sub Reportes_Mesp()Dim rRange As Range, filRange As Range, Rng As Range
Dim oList As ListObject
Dim rngFiltCol As Range
Dim dDate As Date
Dim strDate As String
Dim Path As String
Dim SaldoAnterior As String
Dim LastVisibleRow As Long
Dim OpenFileName As String
Dim wb As Workbook
Dim wi As Workbook

Range("A2").Select

With ActiveSheet.AutoFilter.Range

.AutoFilter Field:=17, Criteria1:="SIM", Operator:=xlAnd

Set rngFiltCol = .Offset(1, 0).Resize(.Rows.Count - 1, 1)

If rngFiltCol.Cells.Count = 1 Then 'Only column headers visible
MsgBox "Nada a Reportar. Verificar mapa!"
'Following line turns off all filters
For Each oList In ActiveSheet.ListObjects
oList.AutoFilter.ShowAllData
Next oList
Exit Sub
'Alternative examples to turn off individual filters
'.AutoFilter Field:=5
'.AutoFilter Field:=14
End If

For Each Rng In rngFiltCol.SpecialCells(12).Rows

OpenFileName = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\01. Diário\01. MEA" & Range("B" & Rng.Row) & "" & Range("B" & Rng.Row) & " - " & Range("D" & Rng.Row) & " - " & Range("C" & Rng.Row) & ".xlsx"
OpenFileNames = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\02. MESP\Template\Template_Extracto_Excel_MESP_010917.xls"
Path = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\02. MESP\2017\01. MEA\10" & Range("B" & Rng.Row) & " - " & Range("D" & Rng.Row) & " - " & Range("C" & Rng.Row) & ".xlsm"
SaldoAnterior = Format(Range("O" & Rng.Row), "##,##0.00")

If OpenFileName = "False" Then Exit Sub
Set wi = Workbooks.Open(OpenFileNames, UpdateLinks:=0)
Set wb = Workbooks.Open(OpenFileName, UpdateLinks:=0)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveSheet.AutoFilter.Range
.AutoFilter Field:=1, Operator:=xlAnd, _
Criteria1:=">" & Application.EoMonth(Now, -2), _
Criteria2:="<=" & Application.EoMonth(Now, -1)

On Error Resume Next
LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count

On Error Resume Next
'Get data EXAMPLE
wi.Sheets("Data Operação").Range("L5") = SaldoAnterior
wi.Sheets("Data Operação").Range("G6:G" & LastVisibleRow).Value = wb.Sheets(1).Range("A10:A" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("H6:H" & LastVisibleRow).Value = wb.Sheets(1).Range("D10:D" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("I6:I" & LastVisibleRow).Value = wb.Sheets(1).Range("E10:E" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("L6:L" & LastVisibleRow).Value = wb.Sheets(1).Range("F10:F" & LastRow).SpecialCells(xlCellTypeVisible).Value

wi.SaveAs Filename:=Path, _
FileFormat:=52, CreateBackup:=False

wb.Close SaveChanges:=False
wi.Close SaveChanges:=False
MsgBox (LastVisibleRow & " Done " & LastRow)
End With

Next Rng

End With

End Sub

StephenCrump · Nov 1, 2017

Welcome to the Forum!

I have a few questions, because it's not clear what you are trying to do.

- Are you trying to put the value of Q5 into "P" & rngRow, or the value of "P" & rngRow into Q5?

- The code line you have provided won't compile because you haven't specified which sheet:
ThisWorkbook.Sheets.Range("P" & rngRow)

- Are you sure you want to refer to ThisWorkbook. That means the workbook where the VBA code is. All your other code is copying values from wb (a different workbook) to wi (another different workbook)?

- You haven't defined rngrow. Did you mean Rng.Row? All your other code is using LastVisibleRow.

zipatei · Nov 2, 2017

Thank your help Stehen

- Are you trying to put the value of Q5 into "P" & rngRow, or the value of "P" & rngRow into Q5?

I want the value of Q5 into "P" & rng.Row.

- Are you sure you want to refer to ThisWorkbook. That means the workbook where the VBA code is. All your other code is copying values from wb (a different workbook) to wi (another different workbook)?

Yes, i want refer ThisWorkbook. Because i want to make sure the data copied is correct. I need to get the result of the cell Q5 into "P" & rngRow into ThisWorkbook

- You haven't defined rngrow. Did you mean Rng.Row? All your other code is using LastVisibleRow.

Yes, Is should be Rng.Row.

I tried add this code but the reverse happens of what i expected.
ThisWorkbook.Range("P" & Rng.Row).Value = Wi.Sheets("Data Operação").Range("Q5").Value

Sub Reportes_Mesp()Dim rRange As Range, filRange As Range, Rng As Range
Dim oList As ListObject
Dim rngFiltCol As Range
Dim dDate As Date
Dim strDate As String
Dim Path As String
Dim SaldoAnterior As String
Dim LastVisibleRow As Long
Dim OpenFileName As String
Dim wb As Workbook
Dim wi As Workbook

Range("A2").Select

With ActiveSheet.AutoFilter.Range

.AutoFilter Field:=17, Criteria1:="SIM", Operator:=xlAnd

Set rngFiltCol = .Offset(1, 0).Resize(.Rows.Count - 1, 1)

If rngFiltCol.Cells.Count = 1 Then 'Only column headers visible
MsgBox "Nada a Reportar. Verificar mapa!"
'Following line turns off all filters
For Each oList In ActiveSheet.ListObjects
oList.AutoFilter.ShowAllData
Next oList
Exit Sub
'Alternative examples to turn off individual filters
'.AutoFilter Field:=5
'.AutoFilter Field:=14
End If

For Each Rng In rngFiltCol.SpecialCells(12).Rows

OpenFileName = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\01. Diário\01. MEA" & Range("B" & Rng.Row) & "" & Range("B" & Rng.Row) & " - " & Range("D" & Rng.Row) & " - " & Range("C" & Rng.Row) & ".xlsx"
OpenFileNames = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\02. MESP\Template\Template_Extracto_Excel_MESP_010917.xls"
Path = "\\srvmeafserver\DAF\Tesouraria\Tesouraria-Bancos\2. Movim.Banc\02. MESP\2017\01. MEA\10" & Range("B" & Rng.Row) & " - " & Range("D" & Rng.Row) & " - " & Range("C" & Rng.Row) & ".xlsm"
SaldoAnterior = Format(Range("O" & Rng.Row), "##,##0.00")

If OpenFileName = "False" Then Exit Sub
Set wi = Workbooks.Open(OpenFileNames, UpdateLinks:=0)
Set wb = Workbooks.Open(OpenFileName, UpdateLinks:=0)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveSheet.AutoFilter.Range
.AutoFilter Field:=1, Operator:=xlAnd, _
Criteria1:=">" & Application.EoMonth(Now, -2), _
Criteria2:="<=" & Application.EoMonth(Now, -1)

On Error Resume Next
LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count

On Error Resume Next
'Get data EXAMPLE
wi.Sheets("Data Operação").Range("L5") = SaldoAnterior
wi.Sheets("Data Operação").Range("G6:G" & LastVisibleRow).Value = wb.Sheets(1).Range("A10:A" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("H6:H" & LastVisibleRow).Value = wb.Sheets(1).Range("D10:D" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("I6:I" & LastVisibleRow).Value = wb.Sheets(1).Range("E10:E" & LastRow).SpecialCells(xlCellTypeVisible).Value
wi.Sheets("Data Operação").Range("L6:L" & LastVisibleRow).Value = wb.Sheets(1).Range("F10:F" & LastRow).SpecialCells(xlCellTypeVisible).Value

ThisWorkbook.Range("P" & Rng.Row).Value = Wi.Sheets("Data Operação").Range("Q5").Value

wi.SaveAs Filename:=Path, _
FileFormat:=52, CreateBackup:=False

wb.Close SaveChanges:=False
wi.Close SaveChanges:=False
MsgBox (LastVisibleRow & " Done " & LastRow)
End With

Next Rng

End With

End Sub

zipatei · Nov 2, 2017

- The code line you have provided won't compile because you haven't specified which sheet:
ThisWorkbook.Sheets.Range("P" & rngRow)

ThisWorkbook.Sheets("Saldos MEA").Range("P" & Rng.Row).Value = Wi.Sheets("Data Operação").Range("Q5").Value

StephenCrump · Nov 3, 2017

First, change this:

Code:

On Error Resume Next
LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count
On Error Resume Next

'To
On Error Resume Next
LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count
[COLOR=#ff0000][B]On Error GoTo 0[/B][/COLOR]

Otherwise your code might be erroring (e.g. the sheet may not exist, or it may be protected) and you wouldn't know it.

If the code runs OK but still doesn't produce the expected results, put a breakpoint on this line:

ThisWorkbook.Sheets("Saldos MEA").Range("P" & Rng.Row).Value = Wi.Sheets("Data Operação").Range("Q5").Value

and test what's actually happening, e.g. what's the value of Wi.Sheets("Data Operação").Range("Q5"), what the value of Rng.Row?

zipatei · Nov 7, 2017

StephenCrump said:
First, change this:

Code:

On Error Resume Next LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count On Error Resume Next 'To On Error Resume Next LastVisibleRow = .Cells(2, 3).Resize(LastRow - 1, 1).SpecialCells(xlCellTypeVisible).Count [COLOR=#ff0000][B]On Error GoTo 0[/B][/COLOR]

Otherwise your code might be erroring (e.g. the sheet may not exist, or it may be protected) and you wouldn't know it.

If the code runs OK but still doesn't produce the expected results, put a breakpoint on this line:

ThisWorkbook.Sheets("Saldos MEA").Range("P" & Rng.Row).Value = Wi.Sheets("Data Operação").Range("Q5").Value

and test what's actually happening, e.g. what's the value of Wi.Sheets("Data Operação").Range("Q5"), what the value of Rng.Row?

Fixed. Thank you very much

Range Cell

zipatei

New Member

Excel Facts

StephenCrump

MrExcel MVP

zipatei

New Member

zipatei

New Member

StephenCrump

MrExcel MVP

zipatei

New Member

Similar threads

Forum statistics

Share this page

Range Cell

zipatei

New Member

Excel Facts

StephenCrump

MrExcel MVP

zipatei

New Member

zipatei

New Member

StephenCrump

MrExcel MVP

zipatei

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock