CODEWITHSUNDEEP
vbaexcel
Prasidha Dibya
Prasidha Dibya

Reputation: 13

Error getting variable from other sheets excel macro vba

I'm new to excel. I have some error problem getting values from other sheets using macro vba.

This is my sheets

This is my code in macro vba :

Sub inputDataInvestment()
If ThisWorkbook.Sheets("ACTIVITY").Range("C5").Value = "FUNDING" Then

Dim Baris, totalBaris As Long
totalBaris = ThisWorkbook.Sheets("FUNDING HISTORY").Cells.Rows.Count
Baris = ThisWorkbook.Sheets("FUNDING HISTORY").Cells(totalBaris, 2).End(xlUp).Row + 1

    ThisWorkbook.Sheets("FUNDING HISTORY").Range("A" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value
    ThisWorkbook.Sheets("FUNDING HISTORY").Range("B" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E12").Value
    ThisWorkbook.Sheets("FUNDING HISTORY").Range("C" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E13").Value

    MsgBox "Funding sudah masuk database"

ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value = ""
ThisWorkbook.Sheets("ACTIVITY").Range("E12").Value = ""

ElseIf ThisWorkbook.Sheets("ACTIVITY").Range("C5").Value = "INVESTING" Then

Dim Baris1, totalBaris1 As Long
totalBaris1 = ThisWorkbook.Sheets("INVESTING HISTORY").Cells.Rows.Count
Baris1 = ThisWorkbook.Sheets("INVESTING HISTORY").Cells(totalBaris1, 2).End(xlUp).Row + 1

    ThisWorkbook.Sheets("INVESTING HISTORY").Range("A" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value
    ThisWorkbook.Sheets("INVESTING HISTORY").Range("B" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E13").Value
    ThisWorkbook.Sheets("INVESTING HISTORY").Range("C" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E9").Value
    ThisWorkbook.Sheets("INVESTING HISTORY").Range("D" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E10").Value
    ThisWorkbook.Sheets("INVESTING HISTORY").Range("E" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E11").Value
    ThisWorkbook.Sheets("INVESTING HISTORY").Range("F" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E12").Value

    MsgBox "Investing sudah masuk database"

ThisWorkbook.Sheets("ACTIVITY")("E8").Value = ""
ThisWorkbook.Sheets("ACTIVITY")("E9").Value = ""
ThisWorkbook.Sheets("ACTIVITY")("E10").Value = ""
ThisWorkbook.Sheets("ACTIVITY")("E11").Value = ""
ThisWorkbook.Sheets("ACTIVITY")("E12").Value = ""

ElseIf ThisWorkbook.Sheets("ACTIVITY").Range("C5").Value = "RETURN OF INVESTMENT" Then

Dim Baris2, totalBaris2 As Long
totalBaris2 = ThisWorkbook.Sheets("RETURN OF INVESTMENT").Cells.Rows.Count
Baris2 = ThisWorkbook.Sheets("RETURN OF INVESTMENT").Cells(totalBaris2, 2).End(xlUp).Row + 1

    ThisWorkbook.Sheets("RETURN OF INVESTMENT").Range("A" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value
    ThisWorkbook.Sheets("RETURN OF INVESTMENT").Range("B" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E9").Value
    ThisWorkbook.Sheets("RETURN OF INVESTMENT").Range("D" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E12").Value

    MsgBox "Funding sudah masuk database"

ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value = ""
ThisWorkbook.Sheets("ACTIVITY").Range("E9").Value = ""
ThisWorkbook.Sheets("ACTIVITY").Range("E12").Value = ""

End If
End Sub

And I'm getting this error : error text

I get an error yellow line at this line :

    ThisWorkbook.Sheets("INVESTING HISTORY").Range("A" & Baris).Value = ThisWorkbook.Sheets("ACTIVITY").Range("E8").Value

I can input the funding, but its getting error when I choose investing and return of investment.

Can you help me for this one? I'm really appreciate if someone can help me. If someone can shorting my code, it was really delightful. Thanks btw.

Upvotes: 1

Views: 110

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Assumptions: In your post (code) Baris , Baris1 and Baris2 suppose to find the first empty row in Column B of their sheets.

Therefore I used the following syntax to find the first empty row (in the example below it's for "FUNDING HISTORY" sheet) :

' Set the "FUNDING HISTORY" sheet 
Set FundHistorySht = ThisWorkbook.Sheets("FUNDING HISTORY")
' find first empty row at the end of column B (skip empty rows in the middle)
Baris = FundHistorySht.Cells(FundHistorySht.Rows.count, 2).End(xlUp).Row + 1

To simplify and have a cleaner code, I used With ThisWorkbook.Sheets("ACTIVITY") , and instead of the Ifs I have Select Case .Range("C5").Value

Note: In the screen-shot you've attached, the name of the third sheet is "RETURN OF INVESTING" , and not "RETURN OF INVESTMENT" like you have in your code. So in my code I have used "RETURN OF INVESTING".

Full Code

Option Explicit

Sub inputDataInvestment()

Dim Baris As Long, totalBaris As Long
Dim Baris1 As Long, totalBaris1 As Long
Dim Baris2 As Long, totalBaris2 As Long

Dim FundHistorySht          As Worksheet
Dim InvestHistorySht        As Worksheet
Dim ROISht                  As Worksheet

Set FundHistorySht = ThisWorkbook.Sheets("FUNDING HISTORY")
Set InvestHistorySht = ThisWorkbook.Sheets("INVESTING HISTORY")
Set ROISht = ThisWorkbook.Sheets("RETURN OF INVESTING") ' << check here what is your sheet's name

With ThisWorkbook.Sheets("ACTIVITY")

    Select Case .Range("C5").Value
        Case "FUNDING"
            'totalBaris = FundHistorySht.Cells.Rows.count
            Baris = FundHistorySht.Cells(FundHistorySht.Rows.count, 2).End(xlUp).Row + 1

            FundHistorySht.Range("A" & Baris).Value = .Range("E8").Value
            FundHistorySht.Range("B" & Baris).Value = .Range("E12").Value
            FundHistorySht.Range("C" & Baris).Value = .Range("E13").Value

            MsgBox "Funding sudah masuk database"

            .Range("E8").Value = ""
            .Range("E12").Value = ""

        Case "INVESTING"
            'totalBaris1 = InvestHistorySht.Cells.Rows.count
            Baris1 = InvestHistorySht.Cells(InvestHistorySht.Rows.count, 2).End(xlUp).Row + 1

            InvestHistorySht.Range("A" & Baris1).Value = .Range("E8").Value
            InvestHistorySht.Range("B" & Baris1).Value = .Range("E13").Value
            InvestHistorySht.Range("C" & Baris1).Value = .Range("E9").Value
            InvestHistorySht.Range("D" & Baris1).Value = .Range("E10").Value
            InvestHistorySht.Range("E" & Baris1).Value = .Range("E11").Value
            InvestHistorySht.Range("F" & Baris1).Value = .Range("E12").Value

            MsgBox "Investing sudah masuk database"

            .Range("E8").Value = ""
            .Range("E9").Value = ""
            .Range("E10").Value = ""
            .Range("E11").Value = ""
            .Range("E12").Value = ""

        Case "RETURN OF INVESTING"  ' << check your sheet's name
            'totalBaris2 = ROISht.Cells.Rows.count
            Baris2 = ROISht.Cells(ROISht.Rows.count, 2).End(xlUp).Row + 1

            ROISht.Range("A" & Baris2).Value = .Range("E8").Value
            ROISht.Range("B" & Baris2).Value = .Range("E9").Value
            ROISht.Range("D" & Baris2).Value = .Range("E12").Value

            MsgBox "Funding sudah masuk database"

            .Range("E8").Value = ""
            .Range("E9").Value = ""
            .Range("E12").Value = ""

    End Select    
End With

End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43593

Just shooting in the dark, w/o testing - Change your code to the following:

Dim Baris as long, totalBaris As Long
Dim Baris1 as long, totalBaris1 As Long
Dim Baris2 as long, totalBaris2 As Long

And run it again. It may be fixed, if you are lucky.

Shooting in the dark number 2: Check whether ThisWorkbook.Sheets("FUNDING HISTORY").Range("A" & Baris).Value is protected. If it is, you cannot write values there.

Upvotes: 1

Related Questions