put data in series by vba (MACRO CORRECTION)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: put data in series by vba (MACRO CORRECTION)

Post by HansV »

The only workbook that is opened by the macro is 1.xls. You explicitly close it without saving it in the line

Code: Select all

        .Parent.Close False
False here means "do not save". There is no reason to change this to True since the macro doesn't change 1.xls in any way, it only reads some data from it.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: put data in series by vba (MACRO CORRECTION)

Post by zyxw1234 »

Great But I have to save the changes made by the macro to H2.xlsb

So may I use
Fn.save for that?
Last edited by zyxw1234 on 13 Jul 2020, 08:23, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: put data in series by vba (MACRO CORRECTION)

Post by HansV »

As I mentioned, the macro does NOT make any changes to 1.xls. It only reads data from the workbook, but it does not change anything. So there is no need to save 1.xls.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: put data in series by vba (MACRO CORRECTION)

Post by zyxw1234 »

Ok my mistake i have to save the file in which macro is placed & after runing the macro we will get the output & that i have to save it
macro & output both will be in same file & i am looking to save the same

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: put data in series by vba (MACRO CORRECTION)

Post by HansV »

Place the following line above End Sub:

Code: Select all

    ThisWorkbook.Save
ThisWorkbook is the workbook that contains the code that is running.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: put data in series by vba (MACRO CORRECTION)

Post by zyxw1234 »

Plz see the attachment pic
Getting error
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: put data in series by vba (MACRO CORRECTION)

Post by HansV »

Why did you add the line

.Parent.Close False

It is not needed at the end of the macro.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: put data in series by vba (MACRO CORRECTION)

Post by zyxw1234 »

Code: Select all

Sub test()
    Dim fn As String, a, r As Range, x, BK As String, myVal
    fn = "C:\Users\**I've been banned**\Desktop\1.xls"
    With Workbooks.Open(fn).Worksheets.Item(1)
        a = .Cells(1).CurrentRegion.Columns("i").Value
        .Parent.Close False
    End With
    With Worksheets.Item(1)
        For Each r In .Range("b2", .Range("b" & Rows.Count).End(xlUp))
            myVal = r.Value
            If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
            x = Application.Match(r.Value, a, 0)
            If IsNumeric(x) Then
                With .Cells(r.Row, Columns.Count).End(xlToLeft)
                    ' *** CHANGED ***
                    If .Column = 2 Then
                        ' If we don't have a value yet, use 1
                        .Cells(1, 2) = 1
                    Else
                        ' Otherwise, increase by 1
                        .Cells(1, 2) = .Value + 1
                    End If
                    ' *** END OF CHANGE ***
                End With
            End If
        Next
    End With
    ThisWorkbook.Save
End Sub
Hence Problem Solved
Thnx Alot HansV Sir for giving ur Precious time & Great Help in solving this problem
Sorry for the Trouble
Have a Awesome Day