[Resolvido] Soma automática condicionada.

[manuel antonio]

Bom dia.

Após algum tempo de ausência, graças aos ensinamentos aqui adquiridos, deparo-me com um obstáculo que não consigo contornar, mesmo após consultas em vários outros sítios.

Tenho uma folha de Excell, com vários valores na Coluna "E" e que pretendo que sejam somados automaticamente quando inseridos e cujo total será transposto para uma Textbox do Userform.

No entanto essa soma dependerá do conteúdo da Coluna "D".

Isto é:

-Se a Célula "D5" contiver um número, será feita a soma do valor inserido em "E5".

O total a passar à Textbox será "E2"+"E3"+"E4"+"E5".

-Se a Célula "D5" contiver letras, não será feita a soma do valor inserido em "E5".

O total a passar à Textbox será "E2"+"E3"+"E4".

-As Células com letras, ocupam a Coluna "D" em posições aleatórias.

-O valor a transpor para a Textbox será o somatório de "E2" até à LastRow, omitindo os valores da Coluna "E" em cujas linhas existam letras nas células da coluna "D".

Julgo que coloquei o problema com a devida clareza.

Não tenho nenhum código base, pois nenhum dos que encontro funciona e quase tudo o que encontro é em função de um objeto colocado na folha e não através de Userform.

Grato pela ajuda que puderem dispensar.

Cumprimentos

M.A.

[FreiNando]

Deixo-te uma função que devolve esse valor condicional.

A função precisa o numero da coluna dos valores e o numero da coluna dos condicionantes. Caso queiras calcular de uma folha que não esteja activa, usa o terceiro argumento.

Atenção que esta função considera 0 como numero.

Public Function SomaCondicional(ColCondicao As Long, ColValores As Long, Optional Folha As Worksheet = Nothing) As Double
Dim rgV As Range, rgC As Range, uLinha As Long
Dim V As Double, I As Long

If Folha Is Nothing Then Set Folha = ActiveSheet

uLinha = Folha.UsedRange.Rows(Folha.UsedRange.Rows.Count).Row
For I = 2 To uLinha
	Set rgC = Folha.Cells(I, ColCondicao)
	Set rgV = Folha.Cells(I, ColValores)
	If WorksheetFunction.IsNumber(rgC) Then V = V + rgV.Value
Next I

SomaCondicional = V
End Function

Para usares esta formula como indicaste, num userform, basta lá colocares a instrução:

TextBox.Text = SomaCondicional(4,5)

ou

TextBox.Text = SomaCondicional(4,5, Worksheets("NomedaFolha"))

[manuel antonio]

Boa noite, FreiNando.

Obrigado pela função disponibilizada. Há no entanto um problema. Não a entendo e talvez por isso não está a funcionar.

Se a folha está ativa, não devo uzar o argumento "Optional"?

Podes explicar onde devo especificar as colunas dos condicionantes e dos valores?

Queres fazer o favor de explicar o que faz cada linha?

Que valores devo alterar?

Desculpa o mau jeito.

Agradeço a tua disponibilidade.

Cumprimentos

M.A.

[manuel antonio]

FreiNando, Muito bom dia.

Acordei bem disposto e fui olhar de novo a tua Função.

Depois de ler bem as tuas instruções, acabei por entender que a função tem de estar separada da instrução atribuída à TextBox, que coloquei no evento "UserForm_Activate".

E então, acabei por perceber que a palavra "precisa" que utilizaste, não quer dizer "necessita", mas sim "refere" ou "já contempla".

Apesar de ainda não entender a função, ela FunCIOna 5 ESTreLAS.

Como preciso de obter mais dois dados referentes a duas outras colunas, posso acrescentá-las na Função para obter os valores, certo?

Um excelente fim de semana.

Um abraço.

Manuel António

[FreiNando]

Eu explico um pouco:

Em programação as instruções são agrupadas em procedimentos, que depois podem ser usados por instruções, que até podem estar dentro do próprio procedimento (recursividade).

No VBA podemos definir procedimentos de acordo com a finalidade. Sub não retorna valor e Function retorna valor.

Nas Sub e Function podem ser colocados argumentos para passagem de dados. Esses argumentos podem ser para entrada de dados e quando usados por referência (ByRef) também podem ser usados para saída. Caso sejam Optional podem ser omitidos, sendo nesse caso considerado o valor por defeito.

Na função que te indiquei usei argumentos para tornar a função versátil. Os dois primeiros indicam as colunas e são obrigatórios, o terceiro argumento é optional e serve para poder usar a função numa folha que não esteja activa, ou até de um outro livro, se não for indicada folha então será usada a folha activa.

Quanto a usares mais colunas para outros condicionantes ao valor, podes incluir mais argumentos para indicar outras colunas (o optional tem de ser o último) e incluir instruções para considerar o valor dessas colunas.

[manuel antonio]

Boa tarde, FreiNando.

Como não entendo nada de VB ou qualquer outra linguagem de programação, faz-me confusão a forma como as variáveis e argumentos são usados, aparentemente sem qualquer ligação aos valores ou células que se pretendem.

Isto por desconhecimento dos atributos que cada argumento pode ter e do que esses atributos podem fazer.

Tudo o que sei, aprendi aqui. Assim, apenas tenho hipótese de aprender a utilizar e possivelmente alterar em função da necessidade, mas dificilmente conseguirei compreender a programação (exceto nos mais básicos procedimentos), já que me faltam as bases.

Por isso, embora tentando e não conseguindo acrescentar argumentos, optei por duplicar a função que disponibilizaste, renomeando a função e atributos, para obter os outros valores.

No entanto aquilo que finalmente me parecia simples, somar os três resultados obtidos, não estava a conseguir.

Julguei que bastava somar os valores das três Textbox, mas não me fazia a soma, apresenta-me os valores todos seguidos.

Depois utilizei as variáveis que dão o valor da função, mas dáva-me o valor de "0".

Finalmente, somei os argumentos ligados às TextBox e então o meu brinquedo, lá fêz o que mandei.

Por agora está resolvido o problema. Só falta melhorar o código, de forma a evitar a triplicação da função. Mas vamos ver se consigo fazê-lo. Podes dar uma olhada e juntar tudo numa função? Se não, paciência.

Public Function ValorPago(ColCondicao As Long, ColValores As Long, Optional Folha As Worksheet = Nothing) As Double

	Dim RgVALOR As Range, RgCOL As Range, uLinha As Long
	Dim VaPg As Double, I As Long

If Folha Is Nothing Then Set Folha = ActiveSheet

	uLinha = Folha.UsedRange.Rows(Folha.UsedRange.Rows.Count).Row
	For I = 2 To uLinha
		Set RgCOL = Folha.Cells(I, 4)
		Set RgVALOR = Folha.Cells(I, 5)

		If WorksheetFunction.IsNumber(RgCOL) Then VaPg = VaPg + RgVALOR.Value
	Next I

	ValorPago = VaPg

End Function
Public Function ValorAmortizado(ColCondicao As Long, ColValores As Long, Optional Folha As Worksheet = Nothing) As Double

	Dim RgAMT As Range, RgCOLUNA As Range, uLinha As Long
	Dim VaAMT As Double, I As Long

If Folha Is Nothing Then Set Folha = ActiveSheet

	uLinha = Folha.UsedRange.Rows(Folha.UsedRange.Rows.Count).Row
	For I = 2 To uLinha
		Set RgCOLUNA = Folha.Cells(I, 4)
		Set RgAMT = Folha.Cells(I, 5)

		If WorksheetFunction.IsNumber(RgCOLUNA) = False Then VaAMT = VaAMT + RgAMT.Value
	Next I

	ValorAmortizado = VaAMT

End Function
Public Function JuroTotal(ColCondicao As Long, ColValores As Long, Optional Folha As Worksheet = Nothing) As Double

	Dim RgJUR As Range, RgCLN As Range, uLinha As Long
	Dim VaJUR As Double, I As Long

If Folha Is Nothing Then Set Folha = ActiveSheet

	uLinha = Folha.UsedRange.Rows(Folha.UsedRange.Rows.Count).Row
	For I = 2 To uLinha
		Set RgCLN = Folha.Cells(I, 4)
		Set RgJUR = Folha.Cells(I, 6)

		If WorksheetFunction.IsNumber(RgCLN) Then VaJUR = VaJUR + RgJUR.Value
	Next I

	JuroTotal = VaJUR

End Function

A finalizar o código tem as instruções para as TextBox:

TextBox9_VPAGO.Text = ValorPago(4, 5, Worksheets("CREDITO HABITAÇÃO"))
TextBox10_AM.Text = ValorAmortizado(4, 5, Worksheets("CREDITO HABITAÇÃO"))
TextBox11_JURPAGO.Text = JuroTotal(4, 6, Worksheets("CREDITO HABITAÇÃO"))
TOTALGERAL = ValorPago(4, 5, Worksheets("CREDITO HABITAÇÃO")) + _
 ValorAmortizado(4, 5, Worksheets("CREDITO HABITAÇÃO")) + JuroTotal(4, 6, Worksheets("CREDITO HABITAÇÃO"))
TextBox12_TOTPG.Value = TOTALGERAL

De qualquer forma, foste impecável na ajuda que prestaste e és decididamente uma mais valia neste forum cheio de gente boa.

Um abraço também para Thoga31, que me ajudou com as Tags.

Obrigado.

Um grande abraço.

Manuel António

Edited October 5, 2013 at 07:54 PM by manuel antonio

[FreiNando]

Numa unica função, fica como indiquei antes:

Public Function ValorCondicional(ColCondicao As Long, ColValores As Long, Optional Folha As Worksheet = Nothing) As Double
	Dim RgVALOR As Range, RgCOL As Range, uLinha As Long
	Dim VaPg As Double, I As Long
	If Folha Is Nothing Then Set Folha = ActiveSheet
	uLinha = Folha.UsedRange.Rows(Folha.UsedRange.Rows.Count).Row
	For I = 2 To uLinha
			Set RgCOL = Folha.Cells(I, ColCondicao)
			Set RgVALOR = Folha.Cells(I, ColValores)
			If WorksheetFunction.IsNumber(RgCOL) Then VaPg = VaPg + RgVALOR.Value
	Next I
	ValorCondicional = VaPg
End Function

O código do formulário é que se adaptar.

Penso que existe uma coluna indicando se é para considerar os valores, e 3 colunas com valores.

A soma dos valores diretamente para o texto da textbox só funciona se estiver dentro de parenteses, mas neste caso convém estar formatado para moeda.

'  D - Coluna condicional		  4
'  E - Coluna do Valor pago		5
'  F - Coluna do Valor Amortizado  6
'  G - Coluna do Juro Pago		 7
Dim VPAGO As Double, VAMORT As Double, JUROPAGO As Double
VPAGO = ValorCondicional(4, 5, Worksheets("CREDITO HABITAÇÃO"))
VAMORT = ValorCondicional(4, 6, Worksheets("CREDITO HABITAÇÃO"))
JUROPAGO = ValorCondicional(4, 7, Worksheets("CREDITO HABITAÇÃO"))
TextBox9_VPAGO.Text = Format(VPAGO, "Currency")
TextBox10_AM.Text = Format(VAMORT, "Currency")
TextBox11_JURPAGO.Text = Format(JUROPAGO, "Currency")
TextBox12_TOTPG.Value = Format(VPAGO + VAMORT + JUROPAGO, "Currency")

E programado à força bruta, fica tudo no formulário e dentro do mesmo procedimento:

Public Sub MostrarValores()
Dim VPAGO As Double, VAMORT As Double, JUROPAGO As Double
Dim  I As Long, ULinha As Long

With ActiveSheet
ULinha = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For I = 2 To ULinha
	If WorksheetFunction.IsNumber(.Cells(I, 4).Value) Then
		VPAGO = VPAGO + .Cells(I, 5).Value
		VAMORT = VAMORT + .Cells(I, 6).Value
		JUROPAGO = JUROPAGO + .Cells(I, 7).Value
	End If
Next I
End With

TextBox9_VPAGO.Text = Format(VPAGO, "Currency")
TextBox10_AM.Text = Format(VAMORT, "Currency")
TextBox11_JURPAGO.Text = Format(JUROPAGO, "Currency")
TextBox12_TOTPG.Value = Format(VPAGO + VAMORT + JUROPAGO, "Currency")
End Sub

Edited October 5, 2013 at 10:13 PM by FreiNando

[manuel antonio]

Boa noite, FreiNando.

Portanto, todo o código se resume à última sub que enviaste, certo?

A ser assim, julgo precisar de uma ligeira alteração, porque embora tenhas deduzido bem no que respeita ao nº de valores que vão ser contabilizados, o nº de colunas não é o mesmo. São apenas duas.

A coluna 4 impõem as condições;

A coluna 5 dá-nos um valor se na 4 contiver números;

A coluna 5 dá-nos outro valor se na 4 contiver Alfanuméricos;

A coluna 6 dá um valor sempre que a 4 contenha números.

Por isso, no código da "sub" ;

Public Sub MostrarValores()
   Dim VPAGO As Double, VAMORT As Double, JUROPAGO As Double
   Dim  I As Long, ULinha As Long

   With ActiveSheet
   ULinha = .UsedRange.Rows(.UsedRange.Rows.Count).Row
   For I = 2 To ULinha
       If WorksheetFunction.IsNumber(.Cells(I, 4).Value) Then
           VPAGO = VPAGO + .Cells(I, 5).Value
           VAMORT = VAMORT + .Cells(I, 6).Value
           JUROPAGO = JUROPAGO + .Cells(I, 7).Value
       End If
   Next I
   End With

   TextBox9_VPAGO.Text = Format(VPAGO, "Currency")
   TextBox10_AM.Text = Format(VAMORT, "Currency")
   TextBox11_JURPAGO.Text = Format(JUROPAGO, "Currency")
   TextBox12_TOTPG.Value = Format(VPAGO + VAMORT + JUROPAGO, "Currency")
End Sub

deve existir um outro procedimento para apurar "VAMORT" que se obtém se :

If WorksheetFunction.IsNumber(.Cells(I, 4).Value) = false Then
VAMORT = VAMORT + .Cells(I, 5).Value
End if

deverá ser criado um novo ciclo "For-Next" ?

Friso no entanto que o código está a funcionar às mil maravilhas, fruto da maravilha que foi o teu trabalho.

A formatação das Textbox para moeda, embora não seja necessária , vou seguir o teu conselho e alterar para "currency", pois para todos os efeitos parece-me mais correto.

Boa semana de trabalho.

Cumprimentos

Manuel António

Edited October 6, 2013 at 11:59 PM by manuel antonio