1

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.

Improve this question
3
  • Nothing to see here, I can't read... Commented Dec 12, 2016 at 11:03
  • you have an error in one of the sheet's names, you have ThisWorkbook.Sheets("RETURN OF INVESTMENT") but in the screen-shot you've provided it's "RETURN OF INVESTING" - this will result to another error Commented Dec 12, 2016 at 11:23
  • Also, If Baris is suppose to be the first available row at Column B, you should use Baris = ThisWorkbook.Sheets("FUNDING HISTORY").Cells(ThisWorkbook.Sheets("FUNDING HISTORY").Rows.Count , 2).End(xlUp).Row + 1 Commented Dec 12, 2016 at 11:27

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

this code so clean and neat, thank you so much for helping, i learn so much from your code. but I still got the same error and at the same place.
hey, I find the error. it was here InvestHistorySht.Range("A" & Baris).Value = .Range("E8").Value. It should be InvestHistorySht.Range("A" & Baris1).Value = .Range("E8").Value. Many thanks to you @Shai Radio. gbu
@PrasidhaDibya try edited code, also fixed baris2 in the third Case
1

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.

Improve this answer

1 Comment

this is really shooting in the dark, next time you should test it. Even if Baris is not defined as Long, even as Variant it works OK also for values of 50000+ (Tested)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.